Problem solve Get help with specific problems with your technologies, process and projects.

Query to find total income for the month at hotel

What is the total income, for the month of July 2001, from occupied rooms at all the Sheridan Hotels? I have created the following query trying to answer the above.
 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_name
As 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

Dig Deeper on Microsoft SQL Server Installation