Create While Loop with Cte

create while loop with cte

If you need table:

;WITH Sec(Number) AS 
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sec
    WHERE Number < 884
) 

SELECT * FROM Sec
OPTION(MAXRECURSION 0)

If you need one string:

;WITH Sec(Number) AS 
(
    SELECT 0 AS Number
    UNION ALL
    SELECT Number + 1
    FROM Sec
    WHERE Number < 884
) 

SELECT STUFF(a.[Str], 1, 1, '')
FROM
(
    SELECT (SELECT ',' + CAST(Number AS NVARCHAR(3)) 
    FROM Sec
    FOR XML PATH(''), TYPE
    ).value('.','varchar(max)') AS [Str] 
) AS a
OPTION(MAXRECURSION 0)

How to re write while loop using cte

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

Common Table Expression in a SQL WHILE loop?

You can't do that. The CTE's scope is only the for the next query. It is effectively actually just a part of the query it precedes. Just like an inline view (sub-query) is part of a larger query.

In your case you'd need to go back to good old temp tables, table variables, etc.

---

Related Topics