Author |
Topic |
ransomsreason
Starting Member
2 Posts |
Posted - 2013-04-03 : 18:24:05
|
Hi, so I'm working with data on mental health patients and I need to compare their scores on an assessment at their admit and their discharge from a program. Some of them have been admitted and discharged multiple times and I'd need a comparison for each time. My data currently looks like this, with a new row for each assessment:And I'd like it to look something like this afterwards:I can imagine it's very complicated to get it exactly how I want it, but any solutions or even pointers in the right direction would be much appreciated! Thanks! |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-04 : 01:09:23
|
[code]WITH PersonDetailsAS(SELECT *,ROW_NUMBER() OVER (PARTITION BY Person,TestInterval ORDER BY Date) AS SeqFROM Table)SELECT pd.Person,pd.Score AS BeforeScore,pd.[Date] AS BeforeDate,pd1.Score AS AfterScore, pd1.[Date] AS AfterDateFROM PersonDetails pdLEFT JOIN PersonDetails pd1ON pd1.Person = pd.PersonAND pd1.TestInterval = pd.TestIntervalAND pd1.Seq = pd.Seq + 1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-04 : 03:03:19
|
[code]DECLARE @tab TABLE (Person varchaR(10), Date DATE, TestInterval varchaR(15), Score INT)INSERT INTO @tabSELECT 'Larry', '3/11/2012', 'Admit', 12 union allSELECT 'Larry', '3/12/2012', 'Discharge', 16 union allSELECT 'Larry', '3/13/2012', 'Admit', 14 union allSELECT 'Larry', '3/14/2012', 'Discharge', 18 union allSELECT 'Sushan', '3/15/2012', 'Admit', 21 union allSELECT 'Sushan', '3/16/2012', 'Discharge', 26 union allSELECT 'Kevin', '3/17/2012', 'Admit', 16 union allSELECT 'Kevin', '3/18/2012', 'Discharge', 19 union allSELECT 'Kevin', '3/19/2012', 'Admit', 17;WITH CTE AS(SELECT *,ROW_NUMBER() OVER (PARTITION BY Person, testInterval ORDER BY Date) AS Seq FROM @tab)SELECT Person, MAX(case when TestInterval = 'Admit' then DATE END) as BeforeDate, MAX(case when TestInterval = 'Discharge' then DATE END) as AfterDate, MAX(case when TestInterval = 'Admit' then Score END) as BeforeScore, MAX(case when TestInterval = 'Discharge' then Score END) as AfterScoreFROM CTEGROUP BY Person, Seq[/code] |
|
|
|
|
|