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
GO
SELECT
vst_int_id,
ISNULL([4981], 'NONE') AS Primary_Payor,
ISNULL([4978], 'NONE') AS Secondary_Payor,
ISNULL([4980], 'NONE') AS Tertiary_Payor,
ISNULL([4979], '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
([4981],
[4978],
[4980],
[4979])) 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.)