У меня немного психического блока по этому поводу.
У меня есть система бронирования гостиничных номеров, и в ней есть такая таблица.
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% занятости.
Проблема
Я хотел бы автоматизировать это в хранимую процедуру.
Можно ли сгруппировать это по месяцам для данного года?
Как я могу гарантировать, что заказы, которые перекрывают границу месяца, обрабатываются надлежащим образом?
Ответ 1 · рейтинг 3 · marc_s
Если вам нужно делать это часто, вы можете добавить эти части месяца и года в качестве постоянных вычисляемых столбцов в свою таблицу и поставить на них индекс:
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 во всех ваших запросах!
Ответ 2 · рейтинг 2 · Kristen
Вы можете "Округлить" дату до 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]
Ответ 4 · No Refunds No Returns
Если вы можете добавить вычисляемый столбец в свою таблицу, сделайте его значением месяц, который вы хотите использовать. Я бы определенно выбрал "хранилище со строкой" или "вычислять" каждый раз.
Что касается охвата месяцев, вам нужно решить, как вы хотите это моделировать. Что делать, если у вас бронь на 3 месяца? 4? 12? Более?
Что касается месяца, вы можете попробовать 6-значное значение, например 200911, чтобы вы могли легко их отсортировать, но сохраните их в целочисленном поле. Если значение вычислено, никто не сможет с ним справиться.
Ответ 5 · Charles Bretana
Пытаться:
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)
