lotusnotes (12/11/2013)
Thank you to all.In a fit of inspiration I have come up with my own solution. Although it gives a slightly different result set, I'd be interested in more optimal solutions. Correct me if I'm wrong but my solution appears to use less io and have a lower estimated subtree cost. I provide files with my solution and also the source data.
Thanks - LotusNotes
-- Your query loses a row from episode 20100.
-- If [Description] can be reliably used to determine the start and end of an episode
-- and [EpisodeNo] is unique to each patient as your code suggests,
-- then my query can be changed to this:
;WITH OrderedData AS (
SELECT *,
RangeStart = CASE WHEN [Description] = 'Admitted' OR Staff_from <> Staff_to THEN 1 ELSE 0 END,
RangeEnd = CASE WHEN [Description] = 'Discharged' OR Staff_from <> Staff_to THEN 1 ELSE 0 END
FROM #Consultant
)
SELECT
currow.PatientID,
currow.EpisodeNo,
[Episode_Start]= currow.Activity_Date,
[Episode_End]= nextrow.Activity_Date,
[Staff]= currow.Staff_to,
[EpisodeType_from] = currow.EpisodeType_to,
[EpisodeType_to] = nextrow.EpisodeType_to
FROM OrderedData currow
OUTER APPLY (
SELECT TOP 1
Activity_Date, EpisodeType_to
FROM OrderedData i
WHERE i.RangeEnd = 1
--AND i.PatientID = currow.PatientID
AND i.EpisodeNo = currow.EpisodeNo
AND i.Activity_Date > currow.Activity_Date
ORDER BY i.Activity_Date
) nextrow
WHERE currow.RangeStart = 1
-- Here's some sample data to test against:
DROP TABLE #Consultant;
WITH Consultant (PatientID, EpisodeNo, Activity_Date, EpisodeType_from, EpisodeType_to, Description, Staff_from, Staff_to, Ward_from, Ward_to) AS (
SELECT 1,10001,'01/04/2013 00:01', NULL,7,'Admitted',NULL,'Mr A', 'Ward Z', 'Ward Z' UNION ALL
SELECT 1,10001,'01/04/2013 08:01', 7,11,'Discharged','Mr A', 'Mr A', 'Ward Z', 'Ward Z' UNION ALL
SELECT 2,10002,'01/04/2013 00:01', NULL,7,'Admitted',NULL,'Mr A', 'Ward Z', 'Ward Z' UNION ALL
SELECT 2,10002,'02/04/2013 00:01', 7,10,'Transfer','Mr A', 'Mr A', 'Ward Z', 'Ward Y' UNION ALL
SELECT 2,10002,'02/04/2013 00:02', 10,10,'Transfer','Mr A', 'Mr A', 'Ward Y', 'Ward X' UNION ALL
SELECT 2,10002,'05/04/2013 00:01', 10,11,'Discharged','Mr A', 'Mr A', 'Ward X', 'Ward X' UNION ALL
SELECT 3,10010,'01/05/2013 00:01', NULL,7,'Admitted', NULL,'Mr A', 'Ward Z', 'Ward Z' UNION ALL
SELECT 3,10010,'05/05/2013 00:01', 7,10,'Transfer','Mr A', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
SELECT 3,10010,'05/05/2013 08:01', 10,10,'Transfer','Mr B', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
SELECT 3,10010,'06/05/2013 00:01', 10,10,'Transfer','Mr B', 'Mr B', 'Ward Z', 'Ward Y' UNION ALL
SELECT 3,10010,'06/05/2013 08:01', 10,10,'Transfer','Mr B', 'Mr C', 'Ward Y', 'Ward Y' UNION ALL
SELECT 3,10010,'07/06/2013 00:01', 10,11,'Discharged','Mr C', 'Mr C', 'Ward Y', 'Ward Y' UNION ALL -- changed date
SELECT 4,20100,'01/05/2013 00:01', NULL,7,'Admitted', NULL,'Mr A', 'Ward Z', 'Ward Z' UNION ALL
SELECT 4,20100,'05/05/2013 00:01', 7,10,'Transfer','Mr A', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
SELECT 4,20100,'05/05/2013 08:01', 10,10,'Transfer','Mr B', 'Mr B', 'Ward Z', 'Ward Z' UNION ALL
SELECT 4,20100,'06/05/2013 00:01', 10,10,'Transfer','Mr B', 'Mr B', 'Ward Z', 'Ward Y' UNION ALL
SELECT 4,20100,'06/05/2013 08:01', 10,10,'Transfer','Mr B', 'Mr C', 'Ward Y', 'Ward Z'
)
SELECT * INTO #Consultant FROM Consultant
CREATE UNIQUE CLUSTERED INDEX ucx_EpisodeNo_Activity_Date ON #Consultant (EpisodeNo, Activity_Date)
-- For a real performance test, this sample data set should be scaled up significantly.
-- I might give it a go over lunch.
----------------------------------------------------------------------------------------
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden