SELECT 
				C.CLAIM_KEY,T.TRANSACTION_ZZ,
				-- Below flag is set same as CLAIM_CHART_CPTCODE Charge correction
				CASE WHEN I.CHG_CORR_FLAG IN ('ORIGINAL', 'REVERSAL') THEN 1 
				WHEN I.CHG_CORR_REASON != 0 THEN 1
				WHEN I.CORR_INV_NUM IS NOT NULL THEN 1
				WHEN I.FSC = 5 THEN 1
				ELSE 0 
				END AS CHARGE_CORRECT_F,
				E.CHART_KEY,
				CPT.PROCEDURE_CODE CPTCODE, 
				DIAG1.ICD_9_CM_CODE ICD9CODE1,
				DIAG2.ICD_9_CM_CODE ICD9CODE2,
				DIAG3.ICD_9_CM_CODE ICD9CODE3,
				DIAG4.ICD_9_CM_CODE IDC9CODE4, 
				T.DX_NUM,
				CASE WHEN A.Component = '1' THEN DIAG1.ICD_9_CM_CODE 
				WHEN A.Component = '2' THEN DIAG2.ICD_9_CM_CODE 
				WHEN A.Component = '3' THEN DIAG3.ICD_9_CM_CODE 
				WHEN A.Component = '4' THEN DIAG4.ICD_9_CM_CODE 
				END ICD9CODE,
				A.Component AS Diagnosis_Number,
				A.Id AS LinkageID 
		FROM 
				STG_P_FC_PATIENT P
				INNER JOIN STG_P_FC_INVOICE I ON P.PATIENT = I.PATIENT and i.GROUP_ZZ != 101 -- remove urgent care data
				INNER JOIN STG_P_TRANSAC T ON T.PATIENT = I.PATIENT
				AND T.INVOICE = I.INVOICE
				AND T.GROUP_ZZ = I.GROUP_ZZ
				INNER JOIN CLAIM C ON C.PATIENT_NBR = I.PATIENT
				AND C.GROUP_NBR = I.GROUP_ZZ AND C.INVOICE = I.INVOICE 
				INNER JOIN ENCOUNTER E ON E.OLD_ENCOUNTER_ID = P.OTH_NUM AND E.OLD_ENCOUNTER_ID >= '20000000'
				INNER JOIN STG_P_DN1_CPTCODE CPT ON T.PROC_ZZ = CPT.RECORD_NUMBER
				INNER JOIN STG_P_DN36_ICD9 DIAG1 ON I.DX_ONE = DIAG1.RECORD_NUMBER
				LEFT JOIN STG_P_DN36_ICD9 DIAG2 ON I.DX_TWO = DIAG2.RECORD_NUMBER
				LEFT JOIN STG_P_DN36_ICD9 DIAG3 ON I.DX_THREE = DIAG3.RECORD_NUMBER
				LEFT JOIN STG_P_DN36_ICD9 DIAG4 ON I.DX_FOUR = DIAG4.RECORD_NUMBER 
				CROSS APPLY 
				( 
					SELECT * FROM SplitString(T.DX_NUM,',') 
				)A
		where 
				E.OLD_ENCOUNTER_ID = '30398208'

SELECT 
GROUP_ZZ, PATIENT, INVOICE, TRANSACTION_ZZ ,DX_NUM ,PROC_ZZ,a.Component,a.Id
into #tmp
FROM STG_P_TRANSAC
CROSS APPLY 
( 
	SELECT * FROM SplitString(DX_NUM,',') 
)A
WHERE PROC_ZZ>0 AND UPDATE_DT BETWEEN @pdteBeginDate AND @pdteEndDate 
 
CREATE  CLUSTERED INDEX [IDX_tmp]
ON [#tmp] 
 ([PATIENT],[GROUP_ZZ],[INVOICE],[TRANSACTION_ZZ],[DX_NUM],[PROC_ZZ])
 
 	SELECT 
	AA.CHART_KEY,
	CLAIM_KEY,
	ICD9CODE,
	CPTCODE,
	TRANSACTION_ZZ, 
	CHARGE_CORRECT_F, 
	Diagnosis_Number, 
	LinkageID
	FROM 
	(
		SELECT 
				C.CLAIM_KEY,T.TRANSACTION_ZZ,
				
				CASE WHEN I.CHG_CORR_FLAG IN ('ORIGINAL', 'REVERSAL') THEN 1 
				WHEN I.CHG_CORR_REASON != 0 THEN 1
				WHEN I.CORR_INV_NUM IS NOT NULL THEN 1
				WHEN I.FSC = 5 THEN 1
				ELSE 0 
				END AS CHARGE_CORRECT_F,
				E.CHART_KEY,
				CPT.PROCEDURE_CODE CPTCODE, 
				CASE 
				WHEN T.Component = '1' THEN DIAG1.ICD_9_CM_CODE 
				WHEN T.Component = '2' THEN DIAG2.ICD_9_CM_CODE 
				WHEN T.Component = '3' THEN DIAG3.ICD_9_CM_CODE 
				WHEN T.Component = '4' THEN DIAG4.ICD_9_CM_CODE 
				END ICD9CODE,
				T.DX_NUM,
				T.Component AS Diagnosis_Number,
				T.Id AS LinkageID 

		FROM 
				STG_P_FC_PATIENT P
				INNER JOIN STG_P_FC_INVOICE I  
					ON P.PATIENT = I.PATIENT and i.GROUP_ZZ != 101 -- remove urgent care data
				INNER JOIN #tmp T ON T.PATIENT = I.PATIENT
				AND T.INVOICE = I.INVOICE
				AND T.GROUP_ZZ = I.GROUP_ZZ
				INNER JOIN CLAIM C ON C.PATIENT_NBR = I.PATIENT
				AND C.GROUP_NBR = I.GROUP_ZZ AND C.INVOICE = I.INVOICE 
				INNER JOIN ENCOUNTER E ON E.OLD_ENCOUNTER_ID = P.OTH_NUM AND E.OLD_ENCOUNTER_ID >= '20000000'
				INNER JOIN STG_P_DN1_CPTCODE CPT ON T.PROC_ZZ = CPT.RECORD_NUMBER
				INNER JOIN STG_P_DN36_ICD9 DIAG1 ON I.DX_ONE = DIAG1.RECORD_NUMBER
				LEFT JOIN STG_P_DN36_ICD9 DIAG2 ON I.DX_TWO = DIAG2.RECORD_NUMBER
				LEFT JOIN STG_P_DN36_ICD9 DIAG3 ON I.DX_THREE = DIAG3.RECORD_NUMBER
				LEFT JOIN STG_P_DN36_ICD9 DIAG4 ON I.DX_FOUR = DIAG4.RECORD_NUMBER

				 
		WHERE 
				(I.UPDATE_DT BETWEEN @pdteBeginDate AND @pdteEndDate )
	)AA