i am doing a project on hotel reservation.in that i have to show room vacancy of selected week in the page.i am taking the booked room count from hotel Booking table.below is few feilds in booking table.
我正在做一个关于酒店预订的项目。我必须在页面中显示所选周的房间空缺。我正在从酒店预订表中预订的房间数量。现在是预订表中的少数费用。
my problem is i have to get sum of HB_Noofrooms between hb_chkDt and hb_chkoDt of each date in selected week.
我的问题是我必须在选定周的每个日期的hb_chkDt和hb_chkoDt之间得到HB_Noofrooms的总和。
ex. suppose if i take current date and check it,then i have to get 2 as sum.
恩。假如我拿当前日期并检查它,那么我必须得到2作为总和。
please help me to solve this.
请帮我解决这个问题。
3 个解决方案
#1
1
DECLARE @BeginDate date='2010-01-01'
DECLARE @EndDate date='2011-01-01'
; With Dates([Date]) as (
SELECT @BeginDate
UNION ALL
SELECT DATEADD(Day,1,Date) FROm Dates
WHERE Dates.date<@EndDate
)
SELECT Dates.Date,(SELECT COUNT(HB_No_Of_Rooms) FROM @MyTable WHERE HB_CkdDT>=Dates.date
AND HB_ChkODt<=Dates.Date
GROUP By HB_No_Of_Rooms
)
FROM Dates
OPTION(MAXRECURSION 0)
#2
1
If you want to retrive the total number of rooms occupied in a week:
如果您想要检索一周内占用的房间总数:
select sum(HB_NoOfRooms) from Booking
where (hb_chkDt >= CONVERT(DATETIME, '2011-09-25 00:00:00') and hb_chkDt < CONVERT(DATETIME, '2011-10-01 00:00:00'))
or (HB_ChkODt >= CONVERT(DATETIME, '2011-09-25 00:00:00') and HB_ChkODt < CONVERT(DATETIME, '2011-10-01 00:00:00'))
If you want to check the total number of room occupied today:
如果您想查看今天占用的房间总数:
select sum(HB_NoOfRooms) FROM Booking
where HB_ChkDt <=SYSDATETIME() AND HB_ChkODt >SYSDATETIME()
#3
1
If you will pass date as parameter for which you need to get vacancy then it's simple query :
如果您将日期作为您需要获得空缺的参数传递,那么它是简单的查询:
SELECT SUM(HB_NoOfRooms) FROM Booking
WHERE HB_ChkDt <= @PassedDt AND HB_ChkODt >= @PassedDt
But if you pass range of dates and want to get date wise vacancy for all in between dates then need to make some complex query.
但是如果你通过了日期范围,并希望在日期之间获得所有日期的明确空缺,那么需要进行一些复杂的查询。
UPDATE : Code to get vacancy for multiple date ranges :
更新:代码以获得多个日期范围的空缺:
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @StartDt DATETIME
DECLARE @EndDt DATETIME
CREATE TABLE #Booking (HB_Id INT, HB_ChkDt DATETIME, HB_ChkODt DATETIME, HB_NoOfRooms INT)
INSERT INTO #Booking VALUES (61, '2011-09-07 13:00:00','2011-09-08 13:00:00',1)
INSERT INTO #Booking VALUES (67, '2011-09-27 13:00:00','2011-09-28 2:00:00',1)
INSERT INTO #Booking VALUES (68, '2011-09-27 13:00:00','2011-09-28 2:00:00',1)
INSERT INTO #Booking VALUES (69, '2011-09-28 13:00:00','2011-09-29 2:00:00',1)
SET @StartDt = '2011-09-27'
SET @EndDt = '2011-09-29'
WHILE @StartDt <= @EndDt
BEGIN
IF @strSQL IS NULL OR @strSQL = ''
SET @strSQL = 'SELECT ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AS Dt, ISNULL(SUM(HB_NoOfRooms),0) AS Vacancy FROM #Booking WHERE CONVERT(VARCHAR(10),HB_ChkDt,102) <= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AND CONVERT(VARCHAR(10),HB_ChkODt,102) >= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''''
ELSE
SET @strSQL = @strSQL + ' UNION ALL SELECT ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AS Dt, ISNULL(SUM(HB_NoOfRooms),0) AS Vacancy FROM #Booking WHERE CONVERT(VARCHAR(10),HB_ChkDt,102) <= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AND CONVERT(VARCHAR(10),HB_ChkODt,102) >= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''''
SET @StartDt = DATEADD(D,1,@StartDt)
END
EXEC (@strSQL)
DROP TABLE #Booking
#1
1
DECLARE @BeginDate date='2010-01-01'
DECLARE @EndDate date='2011-01-01'
; With Dates([Date]) as (
SELECT @BeginDate
UNION ALL
SELECT DATEADD(Day,1,Date) FROm Dates
WHERE Dates.date<@EndDate
)
SELECT Dates.Date,(SELECT COUNT(HB_No_Of_Rooms) FROM @MyTable WHERE HB_CkdDT>=Dates.date
AND HB_ChkODt<=Dates.Date
GROUP By HB_No_Of_Rooms
)
FROM Dates
OPTION(MAXRECURSION 0)
#2
1
If you want to retrive the total number of rooms occupied in a week:
如果您想要检索一周内占用的房间总数:
select sum(HB_NoOfRooms) from Booking
where (hb_chkDt >= CONVERT(DATETIME, '2011-09-25 00:00:00') and hb_chkDt < CONVERT(DATETIME, '2011-10-01 00:00:00'))
or (HB_ChkODt >= CONVERT(DATETIME, '2011-09-25 00:00:00') and HB_ChkODt < CONVERT(DATETIME, '2011-10-01 00:00:00'))
If you want to check the total number of room occupied today:
如果您想查看今天占用的房间总数:
select sum(HB_NoOfRooms) FROM Booking
where HB_ChkDt <=SYSDATETIME() AND HB_ChkODt >SYSDATETIME()
#3
1
If you will pass date as parameter for which you need to get vacancy then it's simple query :
如果您将日期作为您需要获得空缺的参数传递,那么它是简单的查询:
SELECT SUM(HB_NoOfRooms) FROM Booking
WHERE HB_ChkDt <= @PassedDt AND HB_ChkODt >= @PassedDt
But if you pass range of dates and want to get date wise vacancy for all in between dates then need to make some complex query.
但是如果你通过了日期范围,并希望在日期之间获得所有日期的明确空缺,那么需要进行一些复杂的查询。
UPDATE : Code to get vacancy for multiple date ranges :
更新:代码以获得多个日期范围的空缺:
DECLARE @strSQL NVARCHAR(MAX)
DECLARE @StartDt DATETIME
DECLARE @EndDt DATETIME
CREATE TABLE #Booking (HB_Id INT, HB_ChkDt DATETIME, HB_ChkODt DATETIME, HB_NoOfRooms INT)
INSERT INTO #Booking VALUES (61, '2011-09-07 13:00:00','2011-09-08 13:00:00',1)
INSERT INTO #Booking VALUES (67, '2011-09-27 13:00:00','2011-09-28 2:00:00',1)
INSERT INTO #Booking VALUES (68, '2011-09-27 13:00:00','2011-09-28 2:00:00',1)
INSERT INTO #Booking VALUES (69, '2011-09-28 13:00:00','2011-09-29 2:00:00',1)
SET @StartDt = '2011-09-27'
SET @EndDt = '2011-09-29'
WHILE @StartDt <= @EndDt
BEGIN
IF @strSQL IS NULL OR @strSQL = ''
SET @strSQL = 'SELECT ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AS Dt, ISNULL(SUM(HB_NoOfRooms),0) AS Vacancy FROM #Booking WHERE CONVERT(VARCHAR(10),HB_ChkDt,102) <= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AND CONVERT(VARCHAR(10),HB_ChkODt,102) >= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''''
ELSE
SET @strSQL = @strSQL + ' UNION ALL SELECT ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AS Dt, ISNULL(SUM(HB_NoOfRooms),0) AS Vacancy FROM #Booking WHERE CONVERT(VARCHAR(10),HB_ChkDt,102) <= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''' AND CONVERT(VARCHAR(10),HB_ChkODt,102) >= ''' + CAST(CONVERT(VARCHAR(10),@StartDt,102) AS VARCHAR(10)) + ''''
SET @StartDt = DATEADD(D,1,@StartDt)
END
EXEC (@strSQL)
DROP TABLE #Booking