CODEWITHSUNDEEP
mysql
sathish
sathish

Reputation: 800

Mysql Find occurrence of start and end date range between two date columns

enter image description here

From the above table, I will search between start and end date. For Ex., '2011-12-21' to '2011-12-23'. From the above image, 5th and 6th row will come under this date range. ie., 5th row will comprise of 21st and 22nd[2 dates] and 6th row comprise of 23rd[1 dates].

So, my result should be 2X300[ie., rateperday of 5th row] + 1X350[ie., rateperday of 6th row].

ie., [950]

Kindly anyone help.. Thanks,

Upvotes: 0

Views: 406

Answers (1)

newtover
newtover

Reputation: 32094

SELECT
  SUM(
    IF(DATEDIFF(least(validto, '2011-12-23'),
                greatest(validfrom, '2011-12-21')) >= 0,
       DATEDIFF(least(validto, '2011-12-23'),
                greatest(validfrom, '2011-12-21')) + 1,
       NULL) * rateperday
 ) as total
FROM thedata;

Upvotes: 1

Related Questions