sql-server - 如何让 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

基本上，您从原始表中创建一个新的临时表。
然后检查是否存在与同一员工的另一行连续但具有不同 TimeSheetDate 的行的任何实例，然后更新它的 TimeSheetDate。
您在循环中继续执行此操作，直到标志 @NOMOREFORMATTINGNEEDED 设置为 true。将此标志设置为 true 是为了验证没有更多具有不同 TimeSheetDate 的连续记录。我检查了一个的 FromDateTime 与另一个的 ToDateTime 的一分钟之内进行此检查。
让我知道这是否适合您。