mysql SELECT DATEDIFF from rate ranges and get price

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

Solution 1:

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 
  WHERE fromdate <= '2022-02-06' 
  AND todate > '2022-02-02') 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 this condition:

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.

Updated fiddle