I have the following table SEASONS with time periods defining a booking prices for each period day;
ID, STARTDATE, ENDDATE, PRICE
6, 2012-06-01, 2012-06-30, 20
7, 2012-07-01, 2012-07-31, 35
8, 2012-08-01, 2012-08-31, 30
9, 2012-09-01, 2012-09-30, 25
This table defines pricing periods (start and end dates of pricing period with price of booking for each day in that particular pricing period). The question is how to create a query which will return the total price of booking for all days in some given booking period? For example, how to calculate (SELECT?) the total (SUM) booking price for period from 2012-06-10 to 2012-08-20 ?
(Of course one can easily calculate it manually = 21(days in Jun)x20 + 31(days in Jul)x35 + 20(days in Aug)x30 = 2105) How SELECT statement returning that total booking price should look like?
Use DATEDIFF
function:
SELECT SUM(DATEDIFF(ENDDATE,STARTDATE) * PRICE) AS TOTAL_PRICE
FROM SEASONS
WHERE STARTDATE <= '2012-06-10' AND ENDDATE >= '2012-08-20'
Also, we can add a check for booking start/end since they fall somewhere in the middle and you don't need to include all the period...
So:
SET @START='2012-06-10';
SET @END='2012-08-20';
SELECT SUM(
DATEDIFF(
IF(YEAR(ENDDATE)=YEAR(@END) AND MONTH(ENDDATE)=MONTH(@END), @END, ENDDATE),
IF(YEAR(STARTDATE)=YEAR(@START) AND MONTH(STARTDATE)=MONTH(@START), @START, STARTDATE)
)
) AS TOTAL_SUM
FROM SEASONS
WHERE STARTDATE <= @END AND ENDDATE >= @START