Im trying to get the grand total rate If a person checks in on 2/3/22 and checks out on 2/6/2022 . Some days fall under different rate ranges, you will see below I was able to get how many days are in each range, but I need total price. any help would be great. So for example 267 267 281 = 815

Data

Query

SELECT unit, fromdate, todate, day, 
(DATEDIFF( IF (todate > '2022-02-06' , '2022-02-06', todate), 
IF ( fromdate < '2022-02-02' , '2022-02-02' , fromdate )) ) 
AS days FROM rates_new WHERE fromdate <= '2022-02-06' 
AND todate > '2022-02-02' AND Unit = 'motel5'
ORDER BY days ASC

Result

CodePudding user response：

You can use least() and greatest() to get the effective boundary within a range between the end date of the range or of the visit. The use datediff() to get the difference in day, multiply with the price and aggregate using sum() to get the total price for the visit.

SELECT sum(datediff(least(adddate('2022-02-06',
                                  INTERVAL 1 DAY),
                          todate),
                    greatest('2022-02-03',
                             fromdate))
           * day)
       FROM rates_new
       WHERE unit = 'motel5'
             AND fromdate <= '2022-02-06'
             AND todate >= '2022-02-03';

db<>fiddle

CodePudding user response：

I think you can modify your query to something like this:

SET @mindt := '2022-02-02';
SET @maxdt := '2022-02-06';

SELECT unit,
       SUM(days*day)
       FROM
(SELECT unit, fromdate, todate, day,
        DATEDIFF(IF(todate > @maxdt, @maxdt, todate), 
                  IF(fromdate < @mindt, @mindt, fromdate)) AS days
FROM rates_new ) V
GROUP BY unit;

Instead of defining the date in the query, try make use of variables so you'll only need to change the date value from the variable and place the variable accordingly in your query. Then remove these conditions:

....
  WHERE fromdate <= '2022-02-06' 
AND todate > '2022-02-02' AND Unit = 'motel5'
...

OR just remove this

AND Unit = 'motel5'

and turn the query into a subquery. From there you can just do the calculation of SUM(days*day) then group by unit.

Demo fiddle