J. Stacey J. Stacey - 4 months ago 5
SQL Question

Using Pivot for Age Ranges Issue

I am using a pivot to group age ranges from an age column and all of the columns are not being filled as they should. I am taking the sum of LOS (length of stay) and the age range totals should equal the TotalLOS, but currently it is not. Please help!!

This is the T-SQL query:

SELECT *
FROM
(
SELECT [Hosp Name],
Cnty,
Max([Hosp County]) as [FirstOfHosp_County],
Max(Hosp) as UID,
LOS,
Age,
Sum(LOS) as TotalLOS,
CASE
WHEN Age <=17 THEN '0-17'
WHEN Age Between 18 and 64 THEN '18-64'
WHEN Age >=65 THEN '65 Up'
ELSE NULL
END AS AGERANGE
FROM [S1: Basic Source Query]
GROUP BY [Hosp Name], Cnty, Age, [Hosp County], Hosp, LOS
) AS S

PIVOT
(
Sum(S.LOS)
for S.AGERANGE
in ([0-17], [18-64],[65 Up])
) as pvt


I'm converting this query from Access. This is the Access query:

TRANSFORM Sum([S1: Basic Source Query].LOS) AS Days
SELECT [S1: Basic Source Query].[Hosp Name],
[S1: Basic Source Query].Cnty,
FirstOfHosp County: First([S1: Basic Source Query].[Hosp County])
UID: First([S1: Basic Source Query].Hosp)
Total: Sum([S1: Basic Source Query].LOS)
FROM [S1: Basic Source Query]
GROUP BY [S1: Basic Source Query].[Hosp Name],
[S1: Basic Source Query].Cnty
PIVOT Switch([Age] Between 0 And 17,"0-17",[Age] Between 18 And 64,"18-64",[Age]>=65,"65 Up");


This is a sample of the Access data before the pivot. It doesn't have all the columns.

Hosp Hosp Name LOS Age
HOSP301 Apple Hospital 16 92
HOSP301 Apple Hospital 5 34
HOSP301 Apple Hospital 14 85
HOSP301 Apple Hospital 7 21
HOSP301 Apple Hospital 4 79
HOSP301 Apple Hospital 2 26
HOSP301 Apple Hospital 2 19


And once the Access query runs, it turns it into this:

UID Hosp Name TotalLOS 0-17 18-64 65 Up
HOSP301 Apple Hospital 130 117 13
HOSP301 Apple Hospital 5 5
HOSP301 Apple Hospital 722 60 455 207
HOSP301 Apple Hospital 23 17 6
HOSP301 Apple Hospital 15 15
HOSP301 Apple Hospital 6 6
HOSP301 Apple Hospital 3 3
HOSP301 Apple Hospital 32 1 31


This is the incorrect SQL results I am getting. The numbers under the Age Ranges should match the TotalLOS column:

UID Hosp Name Cnty FirstOfHosp_County Age TotalLOS 0-17 18-64 65 Up
HOSP301 Apple Hospital Apple Apple 57 24 4
HOSP301 Apple Hospital Apple Apple 68 13 13
HOSP301 Apple Hospital Apple Apple 69 12 4
HOSP301 Apple Hospital Apple Apple 71 10 10
HOSP301 Apple Hospital Apple Apple 73 8 2
HOSP301 Apple Hospital Apple Apple 74 7 7
HOSP301 Apple Hospital Apple Apple 75 6 6
HOSP301 Apple Hospital Apple Apple 79 34 17
HOSP301 Apple Hospital Bacon Apple 63 2 2
HOSP301 Apple Hospital Bagwin Apple 68 16 16


So as you see the numbers in the ranges don't always match what's in the TotalLOS column. I believe I didn't convert it correctly from Access to T-SQL.

Answer

Not sure what your actual problem is, but if you change the SQL to something like this it might work better:

SELECT [Hosp Name], 
    Cnty, 
    Max([Hosp County]) as [FirstOfHosp_County],
    Max(Hosp) as UID, 
    LOS,
    Sum(LOS) as TotalLOS,
    Sum(CASE WHEN Age <=17 THEN LOS else 0 end) as [0-17],
    Sum(CASE WHEN Age Between 18 and 64 THEN LOS else 0 end) as [18-64],
    Sum(CASE WHEN Age >=65 THEN LOS else 0 end) as [65 Up]
FROM [S1:  Basic Source Query]
GROUP BY [Hosp Name], Cnty, [Hosp County], Hosp, LOS

Edit: Removed Age, it makes no sense as a column.