How to re write while loop using cte

Question

I have two tables, one with Events, the other with episodes. An Episode has a start date and end date, the event has a single date. Both Episodes and Events have one of six Types.

Currently I'm using some fuzzy logic to run an update script on the Events table to set it's ID field to the matching Episode. It does this by checking for the Event date between the Episode start and end, both having the same Type, as well as some other links like same User etc.

Since the Events can sit outside of the Episode, or have a different Type, what I do is loop through a sequence of expanding date ranges (StartDate-1, -2 etc) and also cycle through each Type looking for a match.

I've been reading that while loops aren't very efficient, so was wondering if there was a way to rewrite this nested loop into a CTE function.

I'm using SQL Server 2012. Event List is just a temp table that has all the possible Types with an order to loop through.

My loop currently is:

WHILE @CurrBefore <= @Before and @CurrentAfter <= @After
BEGIN
  SET @Row = 0
  WHILE @Row <= @MaxRow
  BEGIN
    UPDATE P
    SET P.ID = E.ID
    FROM Event P
    OUTER APPLY (SELECT TOP 1 E.Id, E.Type
             FROM Episode E
             WHERE E.User = P.User AND
                   E.Type = CASE WHEN @Row=0 THEN P.Event ELSE (SELECT Event FROM #EventList WHERE RN = @Row) END AND
                   P.Date BETWEEN E.StartDate-@CurrentBefore AND E.EndDate+@CurrentAfter
             ORDER BY P.Date) E
             WHERE P.ID = 0

    INCREMENT @ROW CODE 
    END

INCREMENT @BEFORE/AFTER CODE
END

Sample Data:

IF OBJECT_ID('tempdb..#EventList') IS NOT NULL
BEGIN
    DROP TABLE  #EventList
    CREATE TABLE #EventList(Event  Varchar(50), RN  INT);
    INSERT INTO  #EventList SELECT 'A', 1
    INSERT INTO  #EventList SELECT 'B', 2
    INSERT INTO  #EventList SELECT 'C', 3
    INSERT INTO  #EventList SELECT 'D', 4
    INSERT INTO  #EventList SELECT 'E', 5
    INSERT INTO  #EventList SELECT 'F', 6
END

   CREATE TABLE dbo.Episode ([ID] INT, [Start] DateTime, [End] DateTime, [Type] varchar(1), [User] INT)
    INSERT INTO [dbo].Episode    ([ID], [Start], [End], [Type],[User])
    VALUES
        (1, '2018-07-01 10:00', '2018-07-02 14:00', 'A',10),
        (2, '2018-07-05 6:00', '2018-07-06 13:00', 'A',11),
        (3, '2018-07-03 9:00', '2018-07-04 8:00', 'B',10),
        (4, '2018-07-02 15:00', '2018-07-03 7:00', 'B',12),
        (5, '2018-07-01 1:00', '2018-07-02 8:00', 'C',13),
        (6, '2018-07-01 6:00', '2018-07-01 8:00', 'D',11)

CREATE TABLE dbo.Event ([ID] INT, [Date] DateTime, [Type] varchar(1), [User] INT)
INSERT INTO [dbo].Event    ([ID], [Date], [Type],[User])
VALUES
    (0, '2018-07-01 12:00', 'A',10),
    (0, '2018-07-05 15:00', 'A',11),
    (0, '2018-07-03 13:00', 'C',10),
    (0, '2018-07-10 9:00', 'B',12),
    (0, '2018-07-01 5:00', 'C',10),
    (0, '2018-07-01 10:00', 'D',11)

Expected result, Event now looks like this:

1   2018-07-01 12:00:00.000 A   10
2   2018-07-05 15:00:00.000 A   11
3   2018-07-03 13:00:00.000 C   10
0   2018-07-10 09:00:00.000 B   12
1   2018-07-01 05:00:00.000 C   10
6   2018-07-01 10:00:00.000 D   11

Please edit the question and include some sample data (a dozen or two rows) and the final result you expect to achieve. This example helps everyone to understand the required logic. — Vladimir Baranov
I think you're going to need to explain the "logic" here too; why does the 5th event (0, '2018-07-01 5:00', 'C',10) get an Episode.Id of 1 (only user match) and not 5 (date/type match)? Also you're while loop has UPDATE P -- but P doesn't match any table/alias, and you have CASE...THEN P.Event ELSE but only your temp table has a column named Event -- but if the temp table is P then the UPDATE should fail since there is no ID column in the temp table. — Dave Cullum
It matches due to the requirement of p.User = e.User. Time and Type are flexible. Whoops, must have missed a couple of references when copying the code over. This is a simpler version of my script and only has the base requirements. I'll fix up the references. — Matt
Basically what the code does is match the events to the episodes. The event MUST be for the same User. The Event can sometimes be within the episode, or either side of it. The Type often matches, but can sometimes be different. — Matt
I hope there will come a day when ddl+dml for sample data would be so widly used that I wouldn't feel the need to upvote questions just for that. But it is not this day. — Zohar Peled

Shnugo Shnugo · Accepted Answer · 2018-11-16T09:01:18

I don't know, if I fully got the logic, but this might help to get you running:

USE master;
GO
CREATE DATABASE TestDB
GO
USE TestDB;
GO

CREATE TABLE dbo.Episode ([ID] INT, [Start] DateTime, [End] DateTime, [Type] varchar(1), [User] INT)
    INSERT INTO [dbo].Episode    ([ID], [Start], [End], [Type],[User])
    VALUES
        (1, '2018-07-01 10:00', '2018-07-02 14:00', 'A',10),
        (2, '2018-07-05 6:00', '2018-07-06 13:00', 'A',11),
        (3, '2018-07-03 9:00', '2018-07-04 8:00', 'B',10),
        (4, '2018-07-02 15:00', '2018-07-03 7:00', 'B',12),
        (5, '2018-07-01 1:00', '2018-07-02 8:00', 'C',13),
        (6, '2018-07-01 6:00', '2018-07-01 8:00', 'D',11)

CREATE TABLE dbo.[Event] ([ID] INT, [Date] DateTime, [Type] varchar(1), [User] INT)
INSERT INTO [dbo].[Event]    ([ID], [Date], [Type],[User])
VALUES
    (0, '2018-07-01 12:00', 'A',10),
    (0, '2018-07-05 15:00', 'A',11),
    (0, '2018-07-03 13:00', 'C',10),
    (0, '2018-07-10 9:00', 'B',12),
    (0, '2018-07-01 5:00', 'C',10),
    (0, '2018-07-01 10:00', 'D',11)
GO

CREATE TABLE #EventList(Event  Varchar(50), RN  INT);
INSERT INTO #EventList VALUES ('A', 1),('B', 2),('C', 3),('D', 4),('E', 5),('F', 6);

WITH mathingEpisodes AS
(
    SELECT ev.ID AS evID
          ,ev.[Date] AS evDate
          ,ev.[Type] AS evType
          ,ev.[User] AS evUser
          ,e1.RN AS evRN
          ,ep.ID AS epID
          ,ep.[Type] AS epType
          ,e2.RN AS epRN
    FROM [Event] ev
    LEFT JOIN Episode ep ON ev.[User]=ep.[User] AND ev.[Date] >= ep.[Start] AND ev.[Date] < ep.[End]
    LEFT JOIN #EventList e1 ON ev.[Type]=e1.[Event]
    LEFT JOIN #EventList e2 ON ep.[Type]=e2.[Event]
)
SELECT COALESCE(epID,Closest.ID) AS FittingEpisodeID
      ,me.evDate
      ,evType
      ,evUser
FROM mathingEpisodes me
OUTER APPLY(SELECT TOP 1 * 
            FROM Episode ep
            CROSS APPLY(SELECT ABS(DATEDIFF(SECOND,me.evDate,ep.[Start])) AS DiffToStart
                              ,ABS(DATEDIFF(SECOND,me.evDate,ep.[End])) AS DiffToEnd) Diffs
            CROSS APPLY(SELECT CASE WHEN DiffToStart<DiffToEnd THEN DiffToStart ELSE DiffToEnd END AS Smaller) Diffs2  
            WHERE ep.[User] = me.evUser
            AND   me.epID IS NULL
            ORDER BY Diffs2.Smaller
            ) Closest
ORDER BY evDate;
GO
USE master;
GO
DROP DATABASE TestDB;
GO
DROP TABLE #EventList
GO

The result

1   2018-01-07 05:00:00.000 C   10
6   2018-01-07 10:00:00.000 D   11
1   2018-01-07 12:00:00.000 A   10
3   2018-03-07 13:00:00.000 C   10
2   2018-05-07 15:00:00.000 A   11
4   2018-10-07 09:00:00.000 B   12

Some explanation

In the first cte I try to find fitting episodes (same user and date within range).
The second cte will compute the closest Episode for the same user in all cases, where the first cte did not succeed.

The only difference for this sample is the event for userId=12. My logic will bind this to the closest episode of this user (ID=4), while your expected output shows a zero in this place.

Anyway, my solution is fully set-based, therefore faster than a loop, and should be rather close to your needs. Try to adapt it...

UPDATE Some more thoughts...

I did not get the ghist of your #EventList... I bound the results into the set (you can make it visible by using SELECT * instead of the explicit column list. But this is - assumably - not what you meant...

How to re write while loop using cte

1 Answers

Some explanation

UPDATE Some more thoughts...