Grouping dates by month in an sql server query (stored procedure)

Question

Im having a bit of a mental block on this one.

I got booking system for hotel rooms and it contains a table as so

BookingRoomLink
BookingId (FK)
RoomId (FK)
Start_Date
End_Date

I'd like to query the data to extract occupancy levels for each month. I could do this manually (ie for the past month do something like this).

 SELECT BookingRoomLink.Start_Date,
        BookingRoomLink.End_Date,
        DATEDIFF("d", BookingRoomLink.Start_Date, BookingRoomLink.End_Date) as RoomNights
   FROM BookingRoomLink 
  WHERE BookingRoomLink.Start_Date >= dateadd(m, -1, getdate()) 
    AND BookingRoomLink.End_Date <= GETDATE()

Then i can do a count on the results or similar which would give me the room nights "used" and subtract this against the room nights available in a month.

Eg. 10 rooms x 30 days in the month = 300 possible room nights available. 150 used (result from query) = 50% occupancy.

The problem

Id like to automate this into a stored procedure.

Is it possible to group this into months for a given year?

How would I ensure that bookings which overlap a month boundry are suitable handled?

Answer 1

WITH    (
        SELECT  0
        UNION ALL
        SELECT  m + 1
        FROM    mon
        WHERE   m < 11
        ),
        yr (y) AS
        (
        SELECT  CAST('1990-01-01' AS DATETIME)
        UNION ALL
        SELECT  DATEADD(year, 1, y)
        FROM    yr
        WHERE   y <= GETDATE()
        ),
        dates (smy, emy) AS
        (
        SELECT  DATEADD(month, m, y), DATEADD(month, m + 1, y)
        FROM    yr
        CROSS JOIN
                mon
        ),
        diffs (smy, emy, days) AS
        (
        SELECT  smy, emy, DATEDIFF(day, smy, emy)
        FROM    dates
        )
SELECT  smy,
        roomId,
        CAST(SUM(DATEDIFF(day,
        CASE WHEN start_date < smy THEN smy ELSE start_date END,
        CASE WHEN end_date > emy THEN emy ELSE end_date END
        )) AS FLOAT) / days
FROM    diffs
JOIN    bookings
ON      start_date < emy
        AND end_date >= smy
GROUP BY
        roomId, smy, emy, days