I am in the health industry and am facing a problem currently being handled (legacy code) by nested selects. In a nutshell, a patient visits a hospital and I need to generate a report including the insurance payor information. Currently, the information is being split into information by payor with subselects in the FROM clause and runs for minutes. I approached this with a pivot in another report where I only needed the insuror name like this:
-- STEP 8: GET PAYOR RANK; order primary, secondary, tertiary and quaternary payers by column.
-- ~ 0 second || 20,590 rows
IF OBJECT_ID('tempdb..#Pyr') IS NOT NULL
DROP TABLE #Pyr
SELECT vst_int_id, ISNULL(, 'NONE') AS Primary_Payor, ISNULL(, 'NONE') AS Secondary_Payor, ISNULL(, 'NONE') AS Tertiary_Payor, ISNULL(, 'NONE') AS Quaternary_Payor INTO #Pyr FROM (SELECT vst_int_id, plan_ds, pyr_seq_no FROM #Ins JOIN TPM700_PAYOR_PLAN AS TPM700 ON TPM700.plan_int_id = #Ins.cur_pln_int_id) AS p PIVOT (MAX(plan_ds) FOR pyr_seq_no IN (, , , )) AS pvt GO
What I am trying to do with this report is to include full information on the insurors with address, phone, various keys, and insured name. The output should look like this:
Insurer1_Plan_Cd Insurer1_Plan_Desc Insurer1_Bene_No Insurer1_Subsc_Lst_Nm Insurer1_Subsc_Fst_Nm Insurer1_Group_Nm Insurer1_Pyr_Addr Insurer1_Phone Insurer2_Plan_Cd Insurer2_Plan_Desc Insurer2_Bene_No Insurer2_Subsc_Lst_Nm Insurer2_Subsc_Fst_Nm Insurer2_Group_Nm Insurer2_Pyr_Addr Insurer2_Phone Insurer3_Plan_Cd Insurer3_Plan_Desc Insurer3_Bene_No Insurer3_Subsc_Lst_Nm Insurer3_Subsc_Fst_Nm Insurer3_Group_Nm Insurer3_Pyr_Addr Insurer3_Phone
MOLIN MOLINA HEALTHCARE OF OHIO, INC 12345678901 RABBIT ROGER MCAIDHMO PO BOX 22712 ~ LONG BEACH~ CA~ 908015712 no phone no phone no phone
AnthemMC ANTHEM MEDICARE 23456789012 BUNNY BUGS PO BX 105187 ~ ATLANTA~ GA~ 30348-5187 no phone no phone no phone
MOLIN MOLINA HEALTHCARE OF OHIO, INC 34567890123 COTTONTAIL PETER MCAIDHMO PO BOX 22712 ~ LONG BEACH~ CA~ 908015712 no phone no phone no phone
AnthemMC ANTHEM MEDICARE 45678901234 FOOFOO LITTLE BUNNY PO BX 105187 ~ ATLANTA~ GA~ 30348-5187 no phone no phone no phone
AnthemMC ANTHEM MEDICARE 56789012345 RABBIT JACK PO BX 105187 ~ ATLANTA~ GA~ 30348-5187 no phone no phone no phone
The pivot works well for just the column containing the insuror name, but there has to be a more elegant way other than a spaghetti patch of CASE statements or SUBSELECT s. Any ideas?
(I am not sure of what the preview window is actually showing me, so I may have to edit this to make it display correctly.)