您的位置: 首页  >  技术问答  >  两个日期像日历日之间使用不同的colums动态更新

两个日期像日历日之间使用不同的colums动态更新

分类: 技术问答 • 2022-05-25 20:58:58
问题描述：

CREATE PROCEDURE [dbo].[CalendarMonthly] 
(
    @FROMDATE VARCHAR(25) 
) 
AS 
BEGIN 
SET NOCOUNT ON 

DECLARE @ADate DATETIME 
DECLARE @MonthCount INT 

SET @ADate = @FROMDATE 
SET @MonthCount = (SELECT DAY(EOMONTH(@ADate))) 

DECLARE @tmpTable TABLE 
    (  
    TRoomID INT, DAY1 INT, DAY2 INT,  DAY3 INT, DAY4 INT,   DAY5 INT, DAY6 INT,  DAY7 INT  
    ) 

    INSERT INTO @tmpTable 

    SELECT RM.ROOMID,  
      0,0,0,0,0,0,0 

     FROM RoomMaster AS RM 
     LEFT JOIN RoomTypes AS RT ON RM.RoomTypeID = RT.RoomTypeID 
      WHERE RM.RoomMasterStatus <> 99 

DECLARE @RoomID INT 
DECLARE @ForDate DATE 
DECLARE @dtFromDate DATE 
DECLARE @dtToDate DATE 

SET @dtFromDate = CONVERT(DATE,@FromDate) 
SET @ForDate = @dtFromDate 
SET @dtToDate = CONVERT(DATE,CONVERT(VARCHAR,YEAR(@dtFromDate)) +'-'+ CONVERT(VARCHAR,MONTH(DATEADD(M,1,@dtFromDate)))+'-1') 
SET @dtToDate = DATEADD(D,-1,@dtToDate) 

DECLARE @DayCount INT 

WHILE @ForDate <= @dtToDate 
    BEGIN 

     SET @DayCount = DAY(@ForDate) 

     IF @DayCount = 1 
      BEGIN 
        -- Checkin 
        UPDATE @tmpTable SET DAY1 = 1 

        FROM @tmpTable TT 
        JOIN RoomCheckinDetails AS RCD ON RCD.RoomID = TT.TRoomID 
        JOIN RoomCheckinMaster AS RCM ON RCM.CheckinID = RCD.CheckinID 
         WHERE CONVERT(DATE,RCD.CheckinDate) = @ForDate 
           AND RCD.RoomID = TT.TRoomID 

        -- Expected Checkin 
        UPDATE @tmpTable SET DAY1 = 8 

        FROM @tmpTable TT 
        JOIN RoomBookingDetails AS RBD ON RBD.RoomID = TT.TRoomID 
        JOIN RoomBookingMaster AS RBM ON RBM.ReservationID = RBD.ReservationID 
         WHERE CONVERT(DATE,RBD.ExpectedCheckinDate) = @ForDate 
           AND RBD.RoomID = TT.TRoomID    

      END 
      ELSE IF @DayCount = 2 
      . 
      . 
      . 
      . 
      . 
      . 
      ---upto Day count 7 


     SET @ForDate = DATEADD(Day,1,@ForDate) 

    END 


    SELECT * FROM @tmpTable 


END

我的问题是：两个日期像日历日之间使用不同的colums动态更新

例如：checkindate = 2017年4月6日，checoutdate = 2017年9月6日在比较两个日期我有更新列第1天到第6天的值为0.

+4

什么是你的输入数据集和你预期的输出数据集？你可能不需要一个循环，主要是为了做到这一点 –

+0

你能说一个母语为英语的人来帮助你重新写你的问题吗？我不知道你想问什么。 –

答

除了创建临时表并使用游标更新每组列之外，这是一个基于集合的解决方案，可以避免所有这些问题。

在第一部分，如果你只需要7天那么你可以使用一个common table expression一个简单的数值相符表和Table Value Constructor (Transact-SQL)：

declare @fromdate date = '20170605'; 
;with dates as (
    select 
     [Date]=convert(date,dateadd(day,rn-1,@fromdate)) 
    , rn 
    from (values (1),(2),(3),(4),(5),(6),(7)) t(rn) 
)

否则，您可以生成日期的即席表使用堆叠热膨胀系数在common table expression这样的：

declare @fromdate date = '20170605'; 
declare @thrudate date = dateadd(day,6,@fromdate) 

;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n)) 
, dates as (
    select top (datediff(day, @fromdate, @thrudate)+1) 
     [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate)) 
    , rn = row_number() over(order by (select 1)) 
    from n as deka cross join n as hecto cross join n as kilo 
       cross join n as tenK cross join n as hundredK 
    order by [Date] 
)

然后cross join与roommaster日期，并left join都签入和预订表，看是否有房有一个保留或当前被占用：

, cte as (
select 
    rm.roomid 
    , d.rn 
    , Value = case 
     when rcd.roomid is not null then 1 
     when rbd.roomid is not null then 8 
     else 0 
     end 
from dates d 
    cross join roommaster rm 
    left join roomcheckindetails rcd 
    on rm.roomid = rcd.roomid 
    and d.date >= rcd.checkindate 
    and d.date <= rcd.checkoutdate 
    left join roombookingdetails rbd 
    on rm.roomid = rbd.roomid 
    and d.date >= rbd.expectedcheckindate 
    and d.date <= rbd.expectedcheckoutdate 
where rm.roommasterstatus <> 99 
)

那么对于最后一块，你可以使用条件的聚集或pivot()（任选其一），像这样：

select 
    roomid 
    , Day1 = min(case when rn = 1 then value end) 
    , Day2 = min(case when rn = 2 then value end) 
    , Day3 = min(case when rn = 3 then value end) 
    , Day4 = min(case when rn = 4 then value end) 
    , Day5 = min(case when rn = 5 then value end) 
    , Day6 = min(case when rn = 6 then value end) 
    , Day7 = min(case when rn = 7 then value end) 
from cte 
group by roomid 

select 
    roomid 
    , Day1 = [1] 
    , Day2 = [2] 
    , Day3 = [3] 
    , Day4 = [4] 
    , Day5 = [5] 
    , Day6 = [6] 
    , Day7 = [7] 
from cte 
    pivot (min(value) for rn in ([1],[2],[3],[4],[5],[6],[7]))p

有条件聚合的rextester演示：http://rextester.com/RUJ98491

rextester演示与pivot()：http://rextester.com/YNKU89188

都返回相同的结果对我的演示数据：

+--------+------+------+------+------+------+------+------+ 
| roomid | Day1 | Day2 | Day3 | Day4 | Day5 | Day6 | Day7 | 
+--------+------+------+------+------+------+------+------+ 
|  2 | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 
|  3 | 0 | 8 | 8 | 8 | 8 | 0 | 0 | 
+--------+------+------+------+------+------+------+------+

号码和日历表参考：

相关推荐