How do I make my CTE search through my data again in T-SQL/MSSQL?

Question

Currently, I have a list of time entries for employees that looks like this:

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

Because of how our time is processed, I want rows 6 and 7 to have their TimeSheetDate rolled back a day, since it's technically contiguous time with the rows before it, and it is handled by our system as all occurring on November 10 instead of November 11. It should not roll back row 8 (not immediately after row 7) or row 9 (different employee number).

When I try a nested query, I only catch row 6. When I attempt a CTE without the TimeSheetId (since I'm not sure how to implement a way to count on it), it doesn't know when to stop iterating and bombs out. How can I make sure I catch both rows 6 & 7?

CTE I've attempted:

;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)

Just to double check, row 5 and 6 are classed as contiguous despite there being a full minute between them, unlike the others that end and start at the same time? — iamdave, Nov 30 '20 at 16:27
To add to Dave's comment, how big a gap is needed to consider 2 "near" rows as different dates? Is your current system/data accurate to the nearest minute? Is there special handling for midnight (seems so but you need to verify)? A bit odd that almost all rows have times that fall exactly on 15 minute boundaries. — SMor, Nov 30 '20 at 16:40
@iamdave - The system forces that minute difference (because of midnight/day change). It should be considered contiguous for all other purposes. — Don Ford, Nov 30 '20 at 16:46
@SMor - Yes, the data is accurate to the nearest minute. The GUI used by the technicians forces time to the nearest minute, and times are usually clocked at the 15s (which is why the data looks like that). As above, the system forces a minute between 11:59pm and midnight to distinguish calendar day. Otherwise, the time is considered contiguous. — Don Ford, Nov 30 '20 at 16:47
As an aside, that one minute break? I'd create a view that forces anything at 23:59:00 to 00:00:00 at the next date, which will fix that UI entry oddity, and that ought to give you a much saner base to work from for your _actual_ logic... — jleach, Nov 30 '20 at 17:43
@jleach - It's literally the only time :59:59 occurs in the timestamp on the table. It's not hard to program around, but it is weird. — Don Ford, Nov 30 '20 at 18:35

score 1 · Accepted Answer · answered Nov 30 '20 at 17:34

I attempted a solution using SQL Loops to update the data in iterations until it's all like you want it.

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

Basically you create a new temporary table out of the original table. You then check if there's any instance of a row that is continuous with another row for the same employee but has different TimeSheetDate, and you then update the TimeSheetDate of it. You keep doing this in the loop until the flag @NOMOREFORMATTINGNEEDED is set to true. What sets this flag to true is a check that verifies there are no more continuous records with different TimeSheetDate. I checked that the FromDateTime of one is within one minute of the ToDateTime of the other for this check. Let me know if this works for you.

That worked marvelously! Thank you so much! – Don Ford Nov 30 '20 at 18:34 — Don Ford, Nov 30 '20 at 18:34

How do I make my CTE search through my data again in T-SQL/MSSQL?

1 Answers1