June 5, 2009 at 1:55 pm
SELECT c.caseid,p.MedicareProviderNumber,py.PayorType,c.DischargeDate,c.LengthOfStay,c.Age,c.Sex,c.DischargeStatusID,TotalCharge,TotalCost,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 1) as dx1,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 2) as dx2,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 3) as dx3,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 4) as dx4,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 5) as dx5,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 6) as dx6,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 7) as dx7,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 8) as dx8,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 9) as dx9,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 10) as dx10,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 11) as dx11,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 12) as dx12,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 13) as dx13,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 14) as dx14,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 15) as dx15,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 16) as dx16,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 17) as dx17,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 18) as dx18,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 19) as dx19,
(Select top 1 Code from CaseDXCodes where CaseID = c.CaseID and ListOrder = 20) as dx20,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 1) as px1,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 2) as px2,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 3) as px3,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 4) as px4,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 5) as px5,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 6) as px6,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 7) as px7,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 8) as px8,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 9) as px9,
(Select top 1 Code from CasepxCodes where CaseID = c.CaseID and ListOrder = 10) as px10
FROM Cases c
inner join Providers p on c.ProviderID = p.ProviderId
inner join dbo.temp_CasePayors cpy on c.CaseID = cpy.CaseID
inner join PayorTypes py on cpy.PayorTypeID = py.PayorTypeID
June 5, 2009 at 2:59 pm
Try it like this:
WITH ctePX as (
Select CaseID, ListOrder, MAX(Code)
From CasePXCodes
Where ListOrder Between 1 And 10
Group By CaseID, ListOrder
)
, ctePXList as (
Select CaseID,
Case When ListOrder = 1 Then Code Else NULL End as dx1,
Case When ListOrder = 2 Then Code Else NULL End as dx2,
Case When ListOrder = 3 Then Code Else NULL End as dx3,
Case When ListOrder = 4 Then Code Else NULL End as dx4,
Case When ListOrder = 5 Then Code Else NULL End as dx5,
Case When ListOrder = 6 Then Code Else NULL End as dx6,
Case When ListOrder = 7 Then Code Else NULL End as dx7,
Case When ListOrder = 8 Then Code Else NULL End as dx8,
Case When ListOrder = 9 Then Code Else NULL End as dx9,
Case When ListOrder = 10 Then Code Else NULL End as dx10
From ctePX
)
, cteDX as (
Select CaseID, ListOrder, MAX(Code)
From CaseDXCodes
Where ListOrder Between 1 And 20
Group By CaseID, ListOrder
)
, cteDXList as (
Select CaseID,
Case When ListOrder = 1 Then Code Else NULL End as dx1,
Case When ListOrder = 2 Then Code Else NULL End as dx2,
Case When ListOrder = 3 Then Code Else NULL End as dx3,
Case When ListOrder = 4 Then Code Else NULL End as dx4,
Case When ListOrder = 5 Then Code Else NULL End as dx5,
Case When ListOrder = 6 Then Code Else NULL End as dx6,
Case When ListOrder = 7 Then Code Else NULL End as dx7,
Case When ListOrder = 8 Then Code Else NULL End as dx8,
Case When ListOrder = 9 Then Code Else NULL End as dx9,
Case When ListOrder = 10 Then Code Else NULL End as dx10,
Case When ListOrder = 11 Then Code Else NULL End as dx11,
Case When ListOrder = 12 Then Code Else NULL End as dx12,
Case When ListOrder = 13 Then Code Else NULL End as dx13,
Case When ListOrder = 14 Then Code Else NULL End as dx14,
Case When ListOrder = 15 Then Code Else NULL End as dx15,
Case When ListOrder = 16 Then Code Else NULL End as dx16,
Case When ListOrder = 17 Then Code Else NULL End as dx17,
Case When ListOrder = 18 Then Code Else NULL End as dx18,
Case When ListOrder = 19 Then Code Else NULL End as dx19,
Case When ListOrder = 20 Then Code Else NULL End as dx20
From cteDX
)
SELECT c.caseid,
p.MedicareProviderNumber, py.PayorType,
c.DischargeDate,c.LengthOfStay,c.Age,c.Sex,c.DischargeStatusID,
TotalCharge,TotalCost,
dx.*, px.*
FROM Cases c
inner join Providers p on c.ProviderID = p.ProviderId
inner join dbo.temp_CasePayors cpy on c.CaseID = cpy.CaseID
inner join PayorTypes py on cpy.PayorTypeID = py.PayorTypeID
left join cteDXList as dx on dx.CaseID = c.CaseID
left join ctePXList as px on px.CaseID = c.CaseID
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply