sql - 在 sql server 查询中按月分组日期(存储过程)

Question

我对此有点心理障碍。

我有酒店房间的预订系统，它包含一张 table

BookingRoomLink
BookingId (FK)
RoomId (FK)
开始日期
结束日期

我想查询数据以提取每个月的入住率。我可以手动执行此操作(即在过去的一个月中执行类似的操作)。

 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()

然后我可以对结果或类似的结果进行计数，这会给我“已使用”的间夜数，并将其减去一个月内可用的间夜数。

例如。 10间x 本月 30 天 = 300 个可能的可用房晚。 150 个已使用(查询结果)= 50% 占用率。

问题

我想将其自动化为一个存储过程。

是否可以将给定年份的月份分组？

我如何确保重叠月份边界的预订得到妥善处理？

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