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

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.开发者_C百科 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?

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

If you need to do this often, you could add those month and year parts as persisted computed columns to your table, and put an index on them:

ALTER TABLE dbo.BookingRoomLink 
   ADD StartMonth AS MONTH(Start_Date) PERSISTED

ALTER TABLE dbo.BookingRoomLink 
   ADD StartYear AS Year(Start_Date) PERSISTED

ALTER TABLE dbo.BookingRoomLink 
   ADD EndMonth AS MONTH(End_Date) PERSISTED

ALTER TABLE dbo.BookingRoomLink 
   ADD EndYear AS Year(End_Date) PERSISTED

You could now select these new computed columns, use them in a WHERE clause, GROUP by those columns - and they'll always be up to date based on Start_Date and End_Date - they're not computed everytime you access them --> much faster than just using DATEPART in all your queries!

You could "Round" the date to the 1st of the month, and then GROUP BY on that. Similar to using DatePart, but you still have a valid date, so you can use a Date Range in the WHERE clause before or after doing the Grouping.

SELECT [Date] = DATEADD(Month, DATEDIFF(Month, 0, Start_Date), 0),    -- 1st of the month
       [Bookings] = COUNT(*)
FROM   BookingRoomLink
GROUP BY DATEADD(Month, DATEDIFF(Month, 0, Start_Date), 0)
ORDER BY [Date]

You can use the DATEPART function to get the month number and group by that number.

If You can add a computed column to your table, make its value the month you want to use. I would definitely choose "store with row" vs. compute each time.

As for spanning months, you'll have to decide how you want to model that. What if you have a reservation that spans 3 months? 4? 12? More?

As for the month, you might try a 6-digit value like 200911 so you can easily sort them but keep them in an integer field. If the value is computed, no one will be able to doink with it.

Try:

 Select DateName(month, start_Date) + 
        DateName(Year, Start_Date) as MonthName,
    Sum(DateDiff(Day, Start_Date, 
         Case DateDiff(Month, Start_Date, End_Date)
            When 0 Then End_Date 
            Else DateAdd(day, -day(Start_Date), 
                  DateAdd(day, DateDiff(day, 0, Start_Date), 0)) Bookings
 From BookingRoomLink
 Group By DateName(month, start_Date) + DateName(Year, Start_Date)