I'm working on a booking application and i'm creating a query that calculates the occupation rate of an Hotel by month. To do that i must check the checkin and checkout dates of all bookings and increment some variables and associate them to the corresponding months. I only need to measure this rates on the current year. If a booking has a checkout date or checkin date in wich the month is March (for example) i need to increment the variable that corresponds to the ocupation in that month. And i need to do this for every booking in that year interval.
Booking has a checkin date and a checkout date, the other attributes are not relevant for this query, the input parameters are BeginDate (2015-01-01 ) and EndDate (2015-12-31).
Month Ocupation
[JANUARY] | 29
[FEBRUARY] | 20
[MARCH] | 30
....... | ...
This is what i did so far, i'm kinda lost at this moment. Any help would be appreciated.
Select
SUM(CASE WHEN datename(month, [CheckIn]) = 'January' or datename(month, [CheckOut]) = 'January' THEN 1 ELSE 0 END) January,
SUM(CASE WHEN datename(month, [CheckIn]) = 'February' or datename(month, [CheckOut]) = 'February' THEN 1 ELSE 0 END) February,
SUM(CASE WHEN datename(month, [CheckIn]) = 'March' or datename(month, [CheckOut]) = 'March' THEN 1 ELSE 0 END) March,
SUM(CASE WHEN datename(month, [CheckIn]) = 'April' or datename(month, [CheckOut]) = 'April' THEN 1 ELSE 0 END) April,
SUM(CASE WHEN datename(month, [CheckIn]) = 'May' or datename(month, [CheckOut]) = 'May' THEN 1 ELSE 0 END) May,
SUM(CASE WHEN datename(month, [CheckIn]) = 'June' or datename(month, [CheckOut]) = 'June' THEN 1 ELSE 0 END) June,
SUM(CASE WHEN datename(month, [CheckIn]) = 'July' or datename(month, [CheckOut]) = 'July' THEN 1 ELSE 0 END) July,
SUM(CASE WHEN datename(month, [CheckIn]) = 'August' or datename(month, [CheckOut]) = 'August' THEN 1 ELSE 0 END) August,
SUM(CASE WHEN datename(month, [CheckIn]) = 'September' or datename(month, [CheckOut]) = 'September' THEN 1 ELSE 0 END) September,
SUM(CASE WHEN datename(month, [CheckIn]) = 'October' or datename(month, [CheckOut]) = 'October' THEN 1 ELSE 0 END) October,
SUM(CASE WHEN datename(month, [CheckIn]) = 'November' or datename(month, [CheckOut]) = 'November' THEN 1 ELSE 0 END) November,
SUM(CASE WHEN datename(month, [CheckIn]) = 'December' or datename(month, [CheckOut]) = 'December' THEN 1 ELSE 0 END) December
FROM {Booking} INNER JOIN {Status} ON {Booking}.[StatusId] = {Status}.[Id]
WHERE {Booking}.[CheckIn] >= @BeginDate AND {Booking}.[CheckOut] <= @EndDate AND {Status}.[Label] <> 'Canceled'
Assuming that you want to count bookings where CheckIn and CheckOut occurs in the same month as 1 occupation for that month, and bookings that have CheckIn and CheckOut in different months as 1 for each month then this query should work:
SELECT
Month,
'Occupation' = COUNT(MonthNo)
FROM Booking
OUTER APPLY (
SELECT MonthNo, Month FROM (
VALUES (1, 'January'), (2, 'February'), (3, 'March'),(4, 'April'), (5, 'May')
) AS Months (MonthNo, Month)
WHERE MonthNo BETWEEN MONTH(CheckIn) AND MONTH(CheckOut)
) c
-- WHERE CheckIn >= @BeginDate
-- AND CheckOut <= @EndDate
-- AND [Status].[Label] <> 'Canceled'
GROUP BY Month, MonthNo ORDER BY MonthNo
I used a table value constructor to make the table with month numbers and names inline, but if you already have a suitable calendar table (which you should) then use that instead.
Given a input table like:
Id CheckIn CheckOut
1 2015-01-30 2015-03-31 -- 1 for Jan, Feb, Mar
2 2015-01-03 2015-01-05 -- 1 for Jan
3 2015-01-30 2015-02-05 -- 1 for Jan, Feb
4 2015-01-30 2015-01-31 -- 1 for Jan
5 2015-02-01 2015-02-03 -- 1 for Feb
6 2015-03-22 2015-04-01 -- 1 for Mar, Apr
7 2015-03-23 2015-04-03 -- 1 for Mar, Apr
8 2015-03-30 2015-03-31 -- 1 for Mar
9 2015-04-01 2015-04-03 -- 1 for Apr
10 2015-04-28 2015-05-01 -- 1 for Apr, May
11 2015-05-01 2015-05-03 -- 1 for May
it would produce an output like:
Month Occupation
January 4
February 3
March 4
April 4
May 2