This is current code (I changed table names)
select
LTRIM(RTRIM(ISNULL(CLM_ID, '')))
+'~'+ LTRIM(RTRIM(ISNULL(CLM_VRSN_NUM, '')))
+'~'+
CASE
WHEN codes = 'pr' THEN 'P'
WHEN codes LIKE 'proc_%' THEN 'S'
ELSE ''
END --AS PROC_TYPE_CD,
+'~'+ LTRIM(RTRIM(ISNULL(ICD10_PROC_CD, '')))
+'~'+ LTRIM(RTRIM(ISNULL(convert(varchar(10), CLM_PRCSD_OR_PD_DT, 112), '')))
+'~'+
ISNULL(CASE
WHEN codes = 'pr' THEN LTRIM(RTRIM(dt0))
WHEN codes = 'proc_1' THEN LTRIM(RTRIM(dt1))
WHEN codes = 'proc_2' THEN LTRIM(RTRIM(dt2))
WHEN codes = 'proc_3' THEN LTRIM(RTRIM(dt3))
WHEN codes = 'proc_4' THEN LTRIM(RTRIM(dt4))
WHEN codes = 'proc_5' THEN LTRIM(RTRIM(dt5))
WHEN codes = 'proc_6' THEN LTRIM(RTRIM(dt6))
WHEN codes = 'proc_7' THEN LTRIM(RTRIM(dt7))
WHEN codes = 'proc_8' THEN LTRIM(RTRIM(dt8))
WHEN codes = 'proc_9' THEN LTRIM(RTRIM(dt9))
WHEN codes = 'proc_10' THEN LTRIM(RTRIM(dt10))
WHEN codes = 'proc_11' THEN LTRIM(RTRIM(dt11))
WHEN codes = 'proc_12' THEN LTRIM(RTRIM(dt12))
ELSE ''
END, '') --AS PROC_DT
+'}' AS EXTRACT
from
(
select
ISNULL(left(MI.patient_control_nbr, len(MI.patient_control_nbr)-4), '') as CLM_ID,
ISNULL(right(MI.claim_increment_id, 4), '') as CLM_VRSN_NUM,
ISNULL(MI.principal_procedure_cd, '') as pr,
ISNULL(PO.procedure_1_cd, '') as proc_1,
ISNULL(PO.procedure_2_cd, '') as proc_2,
ISNULL(PO.procedure_3_cd, '') as proc_3,
ISNULL(PO.procedure_4_cd, '') as proc_4,
ISNULL(PO.procedure_5_cd, '') as proc_5,
etc,
ISNULL(PO.procedure_12_cd, '') as proc_12,
ISNULL(convert(varchar(10), payment_dt.dt, 112), '') as CLM_PRCSD_OR_PD_DT,
ISNULL(convert(varchar(10), MI.principal_procedure_dt, 112), '') as dt0,
ISNULL(convert(varchar(10), PO.procedure_1_dt, 112), '') as dt1,
ISNULL(convert(varchar(10), PO.procedure_2_dt, 112), '') as dt2,
etc.
ISNULL(convert(varchar(10), PO.procedure_10_dt, 112), '') as dt10,
ISNULL(convert(varchar(10), PO.procedure_11_dt, 112), '') as dt11,
ISNULL(convert(varchar(10), PO.procedure_12_dt, 112), '') as dt12
from
MI
left join sub
ON MI.inst_claim_info_seq = sub.inst_claim_info_seq
left join PO
ON MI.inst_claim_info_seq =PO.inst_claim_info_seq
inner join
(select
max(adjudication_or_payment_dt) as dt,
inst_claim_info_seq as inst_claim_info_seq
from MI
group by inst_claim_info_seq ) as payment_dt
ON MI.inst_claim_info_seq = payment_dt.inst_claim_info_seq
join LFD
ON LFD.load_file_detail_id = MI.load_file_detail_id
where left(MI.patient_control_nbr, len(MI.patient_control_nbr)-4) + right(MI.patient_control_nbr, 4) = MI.patient_control_nbr
and LFD.source_file_type_id in (204,2204,3204)
and LFD.file_status_type_id IN (400,401,450,0)
AND payment_dt.dt BETWEEN @start_cycle_dt --First day of previous month
AND @end_cycle_dt --Last Day of previous month
) src
unpivot
(
ICD10_PROC_CD for codes in (
pr,
proc_1, proc_2, proc_3, proc_4, proc_5, proc_6, proc_7, proc_8, proc_9, proc_10, proc_11, proc_12)
) o
where o.ICD10_PROC_CD <> '';
I changed the table names but other than that the select is as is right now.