Ndtyjky

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

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...