-- header
SELECT Member_Id, Claim_Type, Claim_Ref, Policy_No , Member_Name, Coverage_Type, Provider_Code, 
Admission_Date, Discharge_Date, Duration_Days, Claim_Process_Status, App_Date, App_By, 
Diagnosis_Code, Diagnosis_Desc, Plan_Id, Tot_Amt_Incurred, Tot_Amt_Approved, 
Tot_Amt_Disapproved, AdMedika_Klaim_Id, Ref_No
FROM TmpInsurance.dbo.Tmp_prib_KlaimTo_Header
WHERE Member_Id NOT LIKE '%-%'
AND Provider_Code = 'NPK'
AND Ref_No = '0'


-- detail
SELECT Claim_Ref, Member_Id, Benefit_Code, Benefit_Desc, Benefit_Limit, 
Amt_Incurred, Amt_Approved, Amt_Not_Approved, Coverage_Type, Plan_Id, 
AdMedika_Klaim_Id, Ref_No
FROM TmpInsurance.dbo.Tmp_prib_KlaimTo_Detail
WHERE Member_Id NOT LIKE '%-%'
AND Claim_Ref IN (SELECT Claim_Ref
FROM TmpInsurance.dbo.Tmp_prib_KlaimTo_Header
WHERE Member_Id NOT LIKE '%-%'
AND Provider_Code = 'NPK'
AND Ref_No = '0')

-- cek dgn data nurul

SELECT
	*
FROM
	temp_prib_nurul_header AS tmp INNER JOIN
	(
    SELECT Member_Id, Claim_Type, Claim_Ref, Policy_No , Member_Name, Coverage_Type, Provider_Code, 
    Admission_Date, Discharge_Date, Duration_Days, Claim_Process_Status, App_Date, App_By, 
    Diagnosis_Code, Diagnosis_Desc, Plan_Id, Tot_Amt_Incurred, Tot_Amt_Approved, 
    Tot_Amt_Disapproved, AdMedika_Klaim_Id, Ref_No
    FROM TmpInsurance.dbo.Tmp_prib_KlaimTo_Header
    WHERE Member_Id NOT LIKE '%-%'
    AND Provider_Code = 'NPK'
    AND Ref_No = '0'
	) AS nurul ON tmp.[Column 2] = nurul.Claim_Ref
	              AND tmp.[Column 4] = nurul.Member_Name

-----------------

SELECT 
	kth.*,
	nurul.*
FROM
	dbo.KlaimTo_Header AS kth INNER JOIN
	dbo.temp_prib_nurul_header3 AS nurul ON LEFT(kth.No_Klaim,4)+RIGHT(kth.No_Klaim,6) = nurul.[Column 2]
	                                        --AND kth.Member_Id = nurul.[Column 0]
	                                        AND kth.Tot_Amt_Approved = nurul.[Column 17]
	                                        AND kth.Tot_Amt_Disapproved = nurul.[Column 18]
WHERE
  kth.Member_Id <> nurul.[Column 0]

SELECT TOP 10 * FROM dbo.KlaimTo_Header ORDER BY Create_Date DESC
SELECT * FROM	dbo.temp_prib_nurul_header3
