SELECT h.hotel_name,SUM(r.price * DateDiff("d", [b.date_from], [b.date_to])) AS `TotalEarningForJuly2001` FROM ROOM r,BOOKING b,HOTEL h WHERE r.room_no = b.room_no AND r.hotel_no = h.hotel_no AND b.date_from BETWEEN #1/07/2001# AND #31/07/2001# AND b.date_to BETWEEN #2/07/2001# AND #31/07/2001# AND h.hotel_name LIKE '%SHERIDAN%' GROUP BY h.hotel_nameAs you can see, I am just learning SQL. I have a problem calculating the number of days between two dates that are in a particular month, in this case, July 2001. However from my query above, the date difference I calculated is DateDiff("d",b.date_from], [b.date_to]). This is not what I really want. I want number of days in just July. e.g if customer stays from 01/06/2001 - 01/08/2001 my date difference will calculate something like 61 where I need just 31, number of days just for July. I would appreciate your help here.
I have rewritten your query with something that I think may be more suitable. You were basically on the right track...
however your code doesn't seem to allow for the situation where someone checked in in June and checked out in July. Using the CASE statement, I can check the dates that I am using within the aggregation are within July, else I set them to their respective month boundary.
SELECT h.hotel_name, SUM( r.price * DateDiff("d", case when b.date_from <'20020701' then '20020701' else b.date_from end, case when b.date_to > '20020731 23:59:59' then '20020731 23:59:59' else b.date_to end) ) AS [TotalEarningForJuly2001] FROM ROOM r inner join BOOKING b on r.room_no = b.room_no inner join HOTEL h on r.hotel_no = h.hotel_no WHERE h.hotel_name LIKE '%SHERIDAN%' AND ( b.date_from BETWEEN '20020701' AND '20020801' OR b.date_to BETWEEN '20020701' AND '20020801') GROUP BY h.hotel_name
For More Information
- Dozens more answers to tough SQL Server questions from Tony Bain are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.