如何让CTE在T-SQL/MSSQL中再次搜索我的数据？

Question

目前，我有一个员工的时间条目列表，如下所示：

TimeSheetId   TechnicianUserId    TimeSheetDate   FromDatetime              ToDatetime
    1215286               4730    2020-11-10      2020-11-10 14:15:00.000   2020-11-10 15:15:00.000
    1215965               4730    2020-11-10      2020-11-10 15:15:00.000   2020-11-10 15:45:00.000
    1215969               4730    2020-11-10      2020-11-10 15:45:00.000   2020-11-10 17:45:00.000
    1215972               4730    2020-11-10      2020-11-10 17:45:00.000   2020-11-10 23:45:00.000
    1215967               4730    2020-11-10      2020-11-10 23:45:00.000   2020-11-10 23:59:00.000
    1215968               4730    2020-11-11      2020-11-11 00:00:00.000   2020-11-11 00:15:00.000
    1215978               4730    2020-11-11      2020-11-11 00:15:00.000   2020-11-11 00:30:00.000
    1215980               4730    2020-11-11      2020-11-11 16:00:00.000   2020-11-11 16:30:00.000
    1215979               4735    2020-11-11      2020-11-11 00:30:00.000   2020-11-11 08:30:00.000

由于我们的时间是如何处理的，我希望第6行和第7行的TimeSheetDate回滚一天，因为从技术上讲，这是与它之前的行连续的时间，并且我们的系统将其处理为都发生在11月10日，而不是11月11日。它不应该回滚第8行(不是紧跟在第7行之后)或第9行(不同的员工编号)。

当我尝试嵌套查询时，我只捕捉到第6行。当我尝试没有TimeSheetId的CTE时(因为我不确定如何实现依赖它的方法)，它不知道何时停止迭代并抛出。如何确保同时捕获第6行和第7行？

我尝试过的CTE：

;WITH CTEDummyData (
                     [TechnicianUserId]
                    ,[TimeSheetDate]
                    ,[FromDatetime]
                    ,[ToDatetime]
                    )
AS (
SELECT [TechnicianUserId]
      ,[TimeSheetDate]
      ,[FromDatetime]
      ,[ToDatetime]
  FROM @DummyTime
 UNION ALL
SELECT [TechnicianUserId]
      ,CASE
            WHEN DATEDIFF(MINUTE,LAG([ToDateTime]) OVER (ORDER BY [FromDatetime]),[FromDatetime]) < 2
                    AND CAST([FromDatetime] AS DATE) <> LAG([TimeSheetDate]) OVER (ORDER BY [FromDatetime])
            THEN DATEADD(DAY,-1,[TimeSheetDate])
            ELSE [TimeSheetDate]
        END
      ,[FromDatetime]
      ,[ToDatetime]
  FROM CTEDummyData
    )
SELECT *
  FROM CTEDummyData
OPTION (MAXRECURSION 24)

Answer 1

我尝试了一种解决方案，使用SQL循环在迭代中更新数据，直到它完全符合您的要求。

DECLARE @NOMOREFORMATTINGNEEDED AS BIT = 0;
IF (EXISTS(SELECT * FROM SYS.tables WHERE name like '%TempDummyTimeTable%'))
BEGIN
    Drop table #TempDummyTimeTable;
END
SELECT * INTO #TempDummyTimeTable FROM DummyTimeTable;

WHILE (@NOMOREFORMATTINGNEEDED = 0)
BEGIN
    update tl set TimeSheetDate = t.TimeSheetDate from #TempDummyTimeTable tl 
                        join #TempDummyTimeTable t on   tl.TechnicianUserId = t.technicianuserid 
                        and ABS(DATEDIFF (MINUTE, tl.FromDatetime, t.ToDatetime)) <= 1
                        and tl.TimeSheetDate <> t.TimeSheetDate 

    IF (not exists(select * from #TempDummyTimeTable tl JOIN #TempDummyTimeTable t on   tl.TechnicianUserId = t.technicianuserid 
                        and ABS(DATEDIFF (MINUTE, tl.FromDatetime, t.ToDatetime)) <= 1
                        and tl.TimeSheetDate <> t.TimeSheetDate ) )
    BEGIN
        SET @NOMOREFORMATTINGNEEDED = 1
    END
END

select * from #TempDummyTimeTable

基本上，您可以在原始表之外创建一个新的临时表。然后检查是否存在与同一employee的另一行连续但具有不同TimeSheetDate的行的任何实例，然后更新它的TimeSheetDate。在循环中一直这样做，直到标志@NOMOREFORMATTINGNEEDED设置为true。将此标志设置为true的是验证不存在具有不同TimeSheetDate的连续记录的检查。对于此检查，我检查了一个的FromDateTime与另一个的ToDateTime相差不到一分钟。如果这对你有效，请告诉我。