MySql Date range manipulation

Question

Table example

ROW-------|---ID---|---FROM_DATE-------|---TO_DATE------|---PRICE---

1---------|---10---|---2014-02-01------|---2014-02-28---|---150.00---

2---------|---22---|---2014-01-10------|---2014-02-09---|---200.00---

3---------|---39---|---2014-02-20------|---2014-03-19---|---120.00---

4---------|---45---|---2014-01-15------|---2014-04-14---|---600.00---

Now, I have to calculate how much money (relatively) each ID has created for February 2014 only.

The first row is easy, all the amount is within February 2014.

In the second row I need to calculate that there are 31 days in January and take 9/31 of the amount.

In the third row is much like the second row, there are 28 days in February, thus 9/28 of the amount.

The forth row is tricky, February doesn't show at all and I know there are 90 days between Jan 15 and Apr 14. therefore I should get 28/90 of the amount.

Wanted result:

ROW-------|---ID---|---MONTH----|---SUM---

1---------|---10---|---02/2014---|---150.00---

2---------|---22---|---02/2014---|---58.06---

3---------|---39---|---02/2014---|---38.57---

4---------|---45---|---02/2014---|---186.66---

Please help me how to do it.

Thanks!!!

Break down your problem. Step 1: determine if there are overlapping date ranges (otherwise no calculations necessary). This is a simple Google search. Step2: If overlap, write out on paper what you have to calculate. Then come back with your code if it still does not work. — Jan Doggen, Mar 03 '14 at 12:55

score 1 · Accepted Answer · answered Mar 03 '14 at 12:47

This is a bit tricky.

Here is one method to count just the overlapping days in February (or any other time period):

select sum(datediff(least(to_date, date('2014-03-01')), greatest(from_date, '2014-02-01'))) as days
from table t
where to_date >= date('2014-02-01') and from_date < date('2014-03-01');

score 0 · Answer 2 · answered Mar 03 '14 at 13:44

Try this query:

SELECT ROW,ID,'02/2014' MONTH,
(PRICE * (CASE WHEN EN < 0 THEN 28 ELSE 28 - EN END -
CASE WHEN ST <= 0 THEN 0 ELSE 28 - ST END))/CNT SUM
FROM
(SELECT DATEDIFF(FROM_DATE,DATE('2014-02-01'))ST,
DATEDIFF(DATE('2014-03-01'),TO_DATE) - 1 EN,
DATEDIFF(TO_DATE,FROM_DATE) + 1 CNT,TABLE1.* FROM TABLE1)T1;

MySql Date range manipulation

2 Answers2

SQL Fiddle