How to re write while loop using cte
2
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
sql-server tsql edited Nov 16 '18 at 10:01
Birel
495414
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
add a comment |
2
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
edited Nov 16 '18 at 10:01
Birel
495414
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
1
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
Nov 16 '18 at 0:07
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 hasUPDATE P-- butPdoesn't match any table/alias, and you haveCASE...THEN P.Event ELSEbut only your temp table has a column namedEvent-- but if the temp table isPthen the UPDATE should fail since there is noIDcolumn in the temp table.
– Dave Cullum
Nov 16 '18 at 3:33
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
Nov 16 '18 at 4:05
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
Nov 16 '18 at 4:10
2
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
Nov 16 '18 at 6:20
add a comment |
2
2
2
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
edited Nov 16 '18 at 10:01
Birel
495414
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
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
edited Nov 16 '18 at 10:01
Birel
495414
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
edited Nov 16 '18 at 10:01
Birel
495414
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
edited Nov 16 '18 at 10:01
Birel
495414
edited Nov 16 '18 at 10:01
Birel
495414
edited Nov 16 '18 at 10:01
Birel
495414
495414
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
asked Nov 15 '18 at 23:31
MattMatt
1,75193355
1,75193355
1
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
Nov 16 '18 at 0:07
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 hasUPDATE P-- butPdoesn't match any table/alias, and you haveCASE...THEN P.Event ELSEbut only your temp table has a column namedEvent-- but if the temp table isPthen the UPDATE should fail since there is noIDcolumn in the temp table.
– Dave Cullum
Nov 16 '18 at 3:33
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
Nov 16 '18 at 4:05
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
Nov 16 '18 at 4:10
2
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
Nov 16 '18 at 6:20
add a comment |
1
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
Nov 16 '18 at 0:07
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 hasUPDATE P-- butPdoesn't match any table/alias, and you haveCASE...THEN P.Event ELSEbut only your temp table has a column namedEvent-- but if the temp table isPthen the UPDATE should fail since there is noIDcolumn in the temp table.
– Dave Cullum
Nov 16 '18 at 3:33
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
Nov 16 '18 at 4:05
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
Nov 16 '18 at 4:10
2
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
Nov 16 '18 at 6:20
1
1
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
Nov 16 '18 at 0:07
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
Nov 16 '18 at 0:07
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
Nov 16 '18 at 3:33
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
Nov 16 '18 at 3:33
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
Nov 16 '18 at 4:05
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
Nov 16 '18 at 4:05
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
Nov 16 '18 at 4:10
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
Nov 16 '18 at 4:10
2
2
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
Nov 16 '18 at 6:20
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
Nov 16 '18 at 6:20
add a comment |
1 Answer
1
active
oldest
votes
2
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...
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
@Matt You can easily add aWHERE MaxDiff ...to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the#EventListin theORDER BYto enforce the right epsiode in case of same distances.
– Shnugo
Nov 19 '18 at 7:45
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329322%2fhow-to-re-write-while-loop-using-cte%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
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...
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
@Matt You can easily add aWHERE MaxDiff ...to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the#EventListin theORDER BYto enforce the right epsiode in case of same distances.
– Shnugo
Nov 19 '18 at 7:45
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
add a comment |
2
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...
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
@Matt You can easily add aWHERE MaxDiff ...to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the#EventListin theORDER BYto enforce the right epsiode in case of same distances.
– Shnugo
Nov 19 '18 at 7:45
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
add a comment |
2
2
2
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...
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
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...
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
answered Nov 16 '18 at 9:01
ShnugoShnugo
50.7k72772
50.7k72772
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
@Matt You can easily add aWHERE MaxDiff ...to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the#EventListin theORDER BYto enforce the right epsiode in case of same distances.
– Shnugo
Nov 19 '18 at 7:45
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
add a comment |
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
@Matt You can easily add aWHERE MaxDiff ...to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the#EventListin theORDER BYto enforce the right epsiode in case of same distances.
– Shnugo
Nov 19 '18 at 7:45
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Basically if there are two Episodes that an Event can match onto, the #EventList acts as an order of precedence. Thanks for this, i'll have a play and see how I go
– Matt
Nov 19 '18 at 3:36
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
Your Userid=12 case might be matching due to the +- modifies to the date ranges. My test was only looking at 2 days either side maximum
– Matt
Nov 19 '18 at 3:38
@Matt You can easily add a
WHERE MaxDiff ... to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the #EventList in the ORDER BY to enforce the right epsiode in case of same distances.– Shnugo
Nov 19 '18 at 7:45
@Matt You can easily add a
WHERE MaxDiff ... to the query to define a maximum distance of matching episodes. And it should be easy as well, to use the #EventList in the ORDER BY to enforce the right epsiode in case of same distances.– Shnugo
Nov 19 '18 at 7:45
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
Yep I've got this working, takes seconds now vs minutes before. I've used a row_number in the first cte to do the ordering, then a simple case of setting some limits and getting the order logic right. I haven't explored what your cross apply code is doing, that's my next task. Thanks for your help
– Matt
Nov 20 '18 at 23:43
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53329322%2fhow-to-re-write-while-loop-using-cte%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
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
Nov 16 '18 at 0:07
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 hasUPDATE P-- butPdoesn't match any table/alias, and you haveCASE...THEN P.Event ELSEbut only your temp table has a column namedEvent-- but if the temp table isPthen the UPDATE should fail since there is noIDcolumn in the temp table.– Dave Cullum
Nov 16 '18 at 3:33
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
Nov 16 '18 at 4:05
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
Nov 16 '18 at 4:10
2
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
Nov 16 '18 at 6:20