Tame this Beast: TSQL Unpivot

Question

Okay... here's the beast:

SELECT
     SUBSTRING(DischDate, 7, 4) + SUBSTRING(DischDate, 1, 2) as YYYYMM
    ,Type
    ,SubType
    ,Diags
    ,Count(*)        as Count
    ,SUM(Charges)    as Charges
    ,SUM(Payments)    as Payments
FROM
    (
        SELECT DISTINCT
             ID
            ,Diags
        FROM 
           (SELECT VisitID as ID
                    ,DX01 ,DX11 ,DX21, DX31
                    ,DX02 ,DX12 ,DX22, DX32
                    ,DX03 ,DX13 ,DX23, DX33
                    ,DX04 ,DX14 ,DX24, DX34
                    ,DX05 ,DX15 ,DX25, DX35
                    ,DX06 ,DX16 ,DX26, DX36
                    ,DX07 ,DX17 ,DX27, DX37
                    ,DX08 ,DX18 ,DX28, DX38
                    ,DX09 ,DX19 ,DX29, DX39
                    ,DX10 ,DX20 ,DX30, DX40
               FROM [AGH00]...[20110128 - AGH00#TXT]) p
            UNPIVOT
               (Diags FOR DX IN
                    (DX01 ,DX11  ,DX21, DX31
                    ,DX02 ,DX12  ,DX22, DX32
                    ,DX03 ,DX13  ,DX23, DX33
                    ,DX04 ,DX14  ,DX24, DX34
                    ,DX05 ,DX15  ,DX25, DX35
                    ,DX06 ,DX16  ,DX26, DX36
                    ,DX07 ,DX17  ,DX27, DX37
                    ,DX08 ,DX18  ,DX28, DX38
                    ,DX09 ,DX19  ,DX29, DX39
                    ,DX10 ,DX20  ,DX30, DX40)
            )AS unpvt
        ) as DIAGS
    LEFT JOIN [AGH00]...[20110128 - AGH00#TXT] as A0 on DIAGS.ID = A0.VisitID
    LEFT JOIN ( SELECT VisitID, Sum(ChargsAmt) as Charges
                FROM [AGH00]...[20110128 - AGH00Chg#TXT]
                Group By VisitID ) as AC on A0.VisitID = AC.VisitID
    LEFT JOIN ( SELECT VisitID, Sum(Pmt) as Payments
                FROM [AGH00]...[20110128 - AGH00Pmt#TXT]
                Group By VisitID ) as AP on A0.VisitID = AP.VisitID                
Group By
     SUBSTRING(DischDate, 7, 4) + SUBSTRING(DischDate, 1, 2)
    ,Type
    ,SubType
    ,Diags
Order By
     SUBSTRING(DischDate, 7, 4) + SUBSTRING(DischDate, 1, 2)
    ,Type
    ,SubType
    ,Diags

Sample output:

YYYYMM Type SubType Diags # Charges    Payments
201004 I    SPEC    5185  3 224086.60  222986.60
200910 I    MONT    73390 2 7573.42    6984.13
200910 O    AHCW    463   4 578.00     578.00
200910 I    MEDI    2766  2 67444.39   67444.39

Notes:

• SQL2005 LinkedServer to a TextFile with a backing ini for field descriptions.

• DischDate is "mm/dd/yyyy" text field. I want YYYYMM for grouping (as well as other fields)

  1. First "table" is a list generated via Unpivot that basically generates a list of VisitID's and unique Diags
  2. Left Join main customer data by "VisitID"
  3. Left Join Charges Data (Grouped to get individual charges grouped by "customer" aka VisitID)
  4. Left Join Payment Data (same as last)
  5. Group by/Order By/Select Date YYYYMMDD, Type, Subtype, Diags... and grouped Count/Sums

More information is in this SO question where someone pointed me to the unpivot option. Including a semi-confused explanation of what I want to accomplish (basically grouping information for a couple similar reports).

Just wondering if there is a more graceful way, and if anyone see potential errors.

Answer 1

This looks like a good use for UNPIVOT. And you can use common table expressions to avoid repeating yourself. Like this:

WITH DIAGS AS
(
    SELECT DISTINCT VisitID as ID, Diags
    FROM 
    (
        SELECT *
        FROM [AGH00]...[20110128 - AGH00#TXT]
    ) p
    UNPIVOT
    (
        Diags FOR DX IN
        (
            DX01, DX11, DX21, DX31
            , DX02, DX12, DX22, DX32
            , DX03, DX13, DX23, DX33
            , DX04, DX14, DX24, DX34
            , DX05, DX15, DX25, DX35
            , DX06, DX16, DX26, DX36
            , DX07, DX17, DX27, DX37
            , DX08, DX18, DX28, DX38
            , DX09, DX19, DX29, DX39
            , DX10, DX20, DX30, DX40
        )
    ) AS unpvt
),
A0 AS
(
    SELECT VisitID, DischDate, [Type], SubType
    FROM [AGH00]...[20110128 - AGH00#TXT]
),
AC AS
(
    SELECT VisitID, SUM(ChargsAmt) AS Charges
    FROM [AGH00]...[20110128 - AGH00Chg#TXT]
    GROUP BY VisitID
),
AP AS
(
    SELECT VisitID, SUM(Pmt) AS Payments
    FROM [AGH00]...[20110128 - AGH00Pmt#TXT]
    GROUP BY VisitID
)
VISITS AS
(
    SELECT SUBSTRING(A0.DischDate, 7, 4) + SUBSTRING(A0.DischDate, 1, 2) AS YYYYMM
        , A0.[Type]
        , A0.SubType
        , DIAGS.Diags
        , AC.Charges
        , AP.Payments
    FROM DIAGS
        LEFT JOIN A0 ON DIAGS.ID = A0.VisitID
        LEFT JOIN AC ON A0.VisitID = AC.VisitID
        LEFT JOIN AP ON A0.VisitID = AP.VisitID                
)
SELECT YYYYMM, [Type], SubType, Diags
    , COUNT(*) AS [Count]
    , SUM(Charges) AS Charges
    , SUM(Payments) AS Payments
FROM VISITS
GROUP BY YYYYMM, [Type], SubType, Diags
ORDER BY YYYYMM, [Type], SubType, Diags