WITH Claim_Aggregates AS(
select
MC004 --Claim Number
,MC005 --Claim Line
,SUM(MC062) as MC062_Sum --Sum of Charge Amount
,SUM(MC063) as MC063_Sum --Sum of Paid Amount
,SUM(MC063C) as MC063C_Sum --Sum of Withhold Amount
,SUM(MC064) as MC064_Sum --Sum of Capitation Amount
,SUM(MC065) as MC065_Sum --Sum of CoPay Amount
,SUM(MC066) as MC066_Sum --Sum of Coinsurance Amount
,SUM(MC067) as MC067_Sum --Sum of Deductible Amount
,SUM(MC095) as MC095_Sum --Sum of COB Amount
,SUM(MC098) as MC098_Sum --Sum of Allowed Amount
,SUM(MC099) as MC099_Sum --Sum of Non-Covered Amount
,SUM(MC121) as MC121_Sum --Sum of Out of Pocket Amount
FROM [data request claims table]
where MC001='71420' AND RecordStatus != -1 -- Include only if RecordStatus is included with data.
-- Typically, only records with RecordStatus = 1 are given to data requesters
group by
MC004 --Claim Number
,MC005 --Claim Line
)
select
SC.MC001 -- SE ID
,SC.MC004 --Claim Number
,SC.MC005 --Claim Line
,SC.MC017 --Paid Date
,SC.MC062 --Charge Amount
,SC.MC063 --Paid Amount
,SC.MC063C --Withhold Amount
,SC.MC064 --Capitation Amount
,SC.MC065 --Copay Amount
,SC.MC066 --Coinsurance Amount
,SC.MC067 --Deductible Amount
,SC.MC095 --COB Amount
,SC.MC098 --Allowed Amount
,SC.MC099 --Non-Covered Amount
,SC.MC121 --Out of Pocket Amount
,CA.MC062_Sum --Sum of Charge Amount
,CA.MC063_Sum --Sum of Paid Amount
,CA.MC063C_Sum --Sum of Withhold Amount
,CA.MC064_Sum --Sum of Capitation Amount
,CA.MC065_Sum --Sum of CoPay Amount
,CA.MC066_Sum --Sum of Coinsurance Amount
,CA.MC067_Sum --Sum of Deductible Amount
,CA.MC095_Sum --Sum of COB Amount
,CA.MC098_Sum --Sum of Allowed Amount
,CA.MC099_Sum --Sum of Non-Covered Amount
,CA.MC121_Sum --Sum of Out of Pocket Amount
FROM [data request claims table] SC
join Claim_Aggregates CA ON CA.MC004 = SC.MC004 and CA.MC005 = SC.MC005
where SC.MC001='71420'
AND RecordStatus != -1 -- Include only if RecordStatus is included with data.
-- Typically, only records with RecordStatus = 1 are given to data requesters
select * from #tmp_71420_base2 where MC004 in ('ATL000000359800','ATL000000360100') order by MC004
select MC706, count(*)
FROM [APCD_DW].[dbo].[StageClaim]
where MC001='71420'
group by MC706
--MC706 = 116 - This versioning approach was created for submitting entities that have processing systems without claims versioning.
-- In this approach, when claim lines are changed, new version numbers are not created. All records are kept.
-- | 