How do I identify movements form one level to another

Question

Post reply

How do I identify movements form one level to another

Nuts

SSCrazy

Points: 2547
More actions
September 2, 2007 at 8:06 pm

#176372

Hi Guys
I have got a patients table which describes how a patient moves from one service to another.
Each service has got a name and level no. associated with it.
For ex a patient who is in dimentia suddenly got sick and he had to be moved to the next level which is the hospital
So I have to calculate how many patients move from one service to another in a course of time.
Each patient has got a start date and end date for each service he was enrolled in.
Here is the sample data
Patient No.   ServiceName ServiceLevel StartDate         EnDDate
1a                  Dementia           1          1/1/2000        1/12/2001
1a                  Hospital             2          1/1/2002          Null
2a                  Hospital             2           12/09/1999       12/10/2004
2a                   Rest home        3            13/10/2004         null
I have to find out for a given service lets say 'dementia', how many people moved to what different service..

Pls help guys

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Can you post some proper expected output base on the sample data above?

N 56°04'39.16"
E 12°55'05.25"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Please don't make multiple posts about the same issue

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=394156

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Nuts SSCrazy Points: 2547 More actions · Answer 3

Hi Peter

The proper expected output would be something like this:

ServiceName Dementia Hospital Rest home Total

Dementia 0 63 2 65

Hospital 4 21 6 31

Rest Home 5 10 0 15

Hi Gail Shaw If you look at the problem carefully its a different question all together

Thanks

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

Something like this, maybe?

-- Prepare sample data

DECLARE @Sample TABLE (Patient SYSNAME, ServiceName SYSNAME, ServiceLevel INT, StartDate DATETIME, EndDate DATETIME)

SET DATEFORMAT DMY

INSERT @Sample

SELECT '1a', 'Dementia', 1, '1/1/2000', '1/12/2001' UNION ALL

SELECT '1a', 'Hospital', 2, '1/1/2002', Null UNION ALL

SELECT '2a', 'Hospital', 2, '12/09/1999', '12/10/2004' UNION ALL

SELECT '2a', 'Rest home', 3, '13/10/2004', null

-- Show the expected output

SELECT s1.ServiceName,

SUM(CASE WHEN s2.ServiceName = 'Dementia' THEN 1 ELSE 0 END) AS Dementia,

SUM(CASE WHEN s2.ServiceName = 'Hospital' THEN 1 ELSE 0 END) AS Hospital,

SUM(CASE WHEN s2.ServiceName = 'Rest home' THEN 1 ELSE 0 END) AS [Rest home],

COUNT(*) AS Total

FROM @Sample AS s1

INNER JOIN @Sample AS s2 ON s2.Patient = s1.Patient AND s2.StartDate > s1.EndDate

GROUP BY s1.ServiceName

ORDER BY s1.ServiceName