Группировка дат по месяцам в запросе sql server (хранимая процедура)

У меня немного психического блока по этому поводу.

У меня есть система бронирования гостиничных номеров, и в ней есть такая таблица.

BookingRoomLink
BookingId (FK)
RoomId (FK)
Start_Date
End_Date

Я хотел бы запросить данные, чтобы извлечь уровни занятости за каждый месяц. Я мог бы сделать это вручную (т.е. за последний месяц сделать что-то вроде этого).

 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% занятости.

Проблема

Я хотел бы автоматизировать это в хранимую процедуру.

Можно ли сгруппировать это по месяцам для данного года?

Как я могу гарантировать, что заказы, которые перекрывают границу месяца, обрабатываются надлежащим образом?


sql sql-server sql-server-2008 date-manipulation
person Matt    schedule 16.11.2009    source источник
comment
Всегда помогают образцы данных и ожидаемые результаты!   -  person JonH    schedule 16.11.2009

Ответы (6)


arrow_upward
3
arrow_downward

Если вам нужно делать это часто, вы можете добавить эти части месяца и года в качестве постоянных вычисляемых столбцов в свою таблицу и поставить на них индекс:

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

Теперь вы можете выбрать эти новые вычисляемые столбцы, использовать их в предложении WHERE, GROUP по этим столбцам - и они всегда будут актуальными на основе Start_Date и End_Date - они не вычисляются каждый раз, когда вы обращаетесь к ним -> намного быстрее чем просто использовать DATEPART во всех ваших запросах!

person marc_s    schedule 16.11.2009

arrow_upward
2
arrow_downward

Вы можете «Округлить» дату до 1-го числа месяца, а затем ГРУППИРОВАТЬ по этому. Подобно использованию DatePart, но у вас все еще есть действительная дата, поэтому вы можете использовать диапазон дат в предложении WHERE до или после выполнения группировки.

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]
person Kristen    schedule 16.11.2009
comment
Мне понравилось это решение. Простой! Я меньше люблю печатать. Спасибо. - person Marcos Buarque; 20.05.2010

arrow_upward
0
arrow_downward

Вы можете использовать функцию DATEPART для получения номера месяца и группировки по этот номер.

person Dave Swersky    schedule 16.11.2009

arrow_upward
0
arrow_downward

Если вы можете добавить вычисляемый столбец в свою таблицу, сделайте его значением месяц, который вы хотите использовать. Я бы определенно выбрал «хранилище со строкой» или «вычислять» каждый раз.

Что касается охвата месяцев, вам нужно решить, как вы хотите это моделировать. Что делать, если у вас бронь на 3 месяца? 4? 12? Более?

Что касается месяца, вы можете попробовать 6-значное значение, например 200911, чтобы вы могли легко их отсортировать, но сохраните их в целочисленном поле. Если значение вычислено, никто не сможет с ним справиться.

person No Refunds No Returns    schedule 16.11.2009

arrow_upward
0
arrow_downward

Пытаться:

 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)
person Charles Bretana    schedule 16.11.2009

arrow_upward

arrow_downward

person    schedule
comment
+1 за использование простого решения, а также с учетом стоянок, которые начинаются до 1-го или заканчиваются после последнего дня месяца - person Tom H; 16.11.2009
comment
sql 2008 предлагает мне это Msg 155, уровень 15, состояние 1, строка 19 'mon' не является признанной опцией dateadd. Сообщение 174, уровень 15, состояние 1, строка 30 Функция dateiff требует 3 аргумента (ов). - person Matt; 16.11.2009