Set based super challenge

I may have an idea on how to do this, I just don't have time to convert the info in your text file into DDL and DML statements to do the work. Please read the first article I reference below in my signature block regarding asking for help. If you convert your information in the text file into a readily consumable format (think cut/paste/execute in ssms), you will get much better answers.

Also, it would help if you would post the code that you have already tried. It is possible that you may have been close and just need a bit more guidance to get the answer.

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

Gosh, I'm surprised that with a title like this, there have been so few takers - and so little feedback from the OP. Maybe those sleepless nights have caught up. Here's a simple solution for starters.

;WITH OrderedData AS (

SELECT *,

RangeStart = CASE WHEN cur.Staff_from IS NULL OR cur.Staff_from <> cur.Staff_to THEN 1 ELSE 0 END,

RangeEnd = CASE WHEN rn = cnt OR cur.Staff_from <> cur.Staff_to THEN 1 ELSE 0 END

FROM (

SELECT *,

rn = ROW_NUMBER() OVER(PARTITION BY c.PatientID, c.EpisodeNo ORDER BY c.Activity_Date),

cnt = COUNT(*) OVER(PARTITION BY c.PatientID, c.EpisodeNo)

FROM #Consultant c

) cur

)

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

I don't know what's going on with the OP but when I see that ChrisM has worked a problem, I figure there's little left for me to do and move on. 😉

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

--Consultant Challenge

-- Use <database>

--Select * from dbo.consultant -- source table

--set statistics io on

with cte1 as

(

Select * from--have to do this to harden the ranking function columns

--so can be used in a where clause

(

SELECT [PatientID]

,[EpisodeNo]

,[Activity_Date]

--,[EpisodeType_from]

--,[EpisodeType]

,[Description]

,[Staff_from]

,[Staff_to]

,row_number() over (partition by EpisodeNo order by Activity_Date) as Row_num

,case

when [Description]='Admitted' or (Staff_from=Staff_to) then 0

else row_number() over (partition by EpisodeNo order by Activity_Date)

end as Staff_change-- any number >0 identifies a staff change

--,[Ward_from]

--,[Ward_to]

FROM [dbo].[consultant]

--order by EpisodeNo-- this may be a sticking point?

) as X

where

Row_num= 1-- admitted

or Row_num=Staff_change--staff change

or Description='Discharged'

) -- end cte1

-- do select * first of all to see all the columns

--select * from

select

a.PatientID

,a.EpisodeNo

,a.Activity_Date as 'From'

,b.Activity_Date as 'To'

,b.Staff_from as 'Consultant'

,case

when b.Description='Discharged' then b.Description+' by '+b.Staff_to

else b.Description+' to '+b.Staff_to

end as 'Result'

from

cte1 a join cte1 b-- self join

on (a.EpisodeNo=b.EpisodeNo)-- groups the rows correctly

and a.Staff_to=b.Staff_from-- to correlate the rows correctly

where

a.Description <>'Discharged'-- gets rid of discharged row

Until you test it on a much larger set of rows, you don't actually know what's better. I can show you two solutions for a different problem where it looks like one solution will take 0% compared to the other which looks like it will take 100%. When you run the code, the diametric opposite happens. Even the actual execution plan shows the same "mistake".

I feel the same way! 😀

I did look at this thread yesterday and saw that Chris suggested there was something wrong with the sample data/results so I passed on it at the time.

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

----------------------------------------------------------------------------------------

I can see it now. Cornish pasty in one hand while the other furiously types in the 1M row test harness.

Mmmm, mmmm! 😛

Thanks Chris. I think you have done enough already 🙂

Good Lord no Dwain, I'd use one of Jeff's fancy shmancy tally tables!!

That's 'cos I always pick the easy ones:-P