RE: Set based super challenge

SSC Guru

Points: 186127

December 10, 2013 at 6:55 am

This doesn't look too challenging. Can you correct both your sample data and your expected output for EpisodeNo 10010? Cheers.

For those of you looking for sample data to play with in advance of LotusNotes posting the corrected versions:

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:01', 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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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