with hassgn as (
select 'A2740'::text as org_id, 'cms_mssp|'::text || "HICN" as fk_patient_id,
	"First Name" as src_bene_first_name, "Last Name" as src_bene_last_name,
    case when "CMS-HCC Risk Score for ESRD Status8" <> '' then 'esrd'
        when "CMS-HCC Risk Score for Disabled Status8" <> '' then 'disabled'
        when "CMS-HCC Risk Score for Aged/Dual Status8" <> '' then 'aged/dual'
        when "CMS-HCC Risk Score for Aged/Non-dual Status8" <> '' then 'aged/non-dual'
    end as cohort_type,
    case when "CMS-HCC Risk Score for ESRD Status8" <> '' then "CMS-HCC Risk Score for ESRD Status8"
        when "CMS-HCC Risk Score for Disabled Status8" <> '' then "CMS-HCC Risk Score for Disabled Status8"
        when "CMS-HCC Risk Score for Aged/Dual Status8" <> '' then "CMS-HCC Risk Score for Aged/Dual Status8"
        when "CMS-HCC Risk Score for Aged/Non-dual Status8" <> '' then "CMS-HCC Risk Score for Aged/Non-dual Status8"
    end as hcc_score,
    case when "Monthly  Eligibility Flag 16" > 0 then 1 else 0 end as month_1_cd,
    case when "Monthly Eligibility Flag 26" > 0 then 1 else 0 end as month_2_cd,
    case when "Monthly Eligibility Flag 36" > 0 then 1 else 0 end as month_3_cd,
    case when "Monthly Eligibility Flag 46" > 0 then 1 else 0 end as month_4_cd,
    case when "Monthly Eligibility Flag 56" > 0 then 1 else 0 end as month_5_cd,
    case when "Monthly Eligibility Flag 66" > 0 then 1 else 0 end as month_6_cd,
    case when "Monthly Eligibility Flag 76" > 0 then 1 else 0 end as month_7_cd,
    case when "Monthly Eligibility Flag 86" > 0 then 1 else 0 end as month_8_cd,
    case when "Monthly Eligibility Flag 96" > 0 then 1 else 0 end as month_9_cd,
    case when "Monthly Eligibility Flag 106" > 0 then 1 else 0 end as month_10_cd,
    case when "Monthly Eligibility Flag 116" > 0 then 1 else 0 end as month_11_cd,
    case when "Monthly Eligibility Flag 126" > 0 then 1 else 0 end as month_12_cd
from sndbx."P.2740.ACO.HASSGN.Y2016"
-- where "Newly Assigned Beneficiary Flag5" = '1'
),

pat_pmpy_mm as (
select org_id, fk_patient_id,
	sum(case when month_cd = 'm-2016-01' then paid_amt else 0 end) as month_1_pmpy,
	sum(case when month_cd = 'm-2016-02' then paid_amt else 0 end) as month_2_pmpy,
	sum(case when month_cd = 'm-2016-03' then paid_amt else 0 end) as month_3_pmpy,
	sum(case when month_cd = 'm-2016-04' then paid_amt else 0 end) as month_4_pmpy,
	sum(case when month_cd = 'm-2016-05' then paid_amt else 0 end) as month_5_pmpy,
	sum(case when month_cd = 'm-2016-06' then paid_amt else 0 end) as month_6_pmpy,
	sum(case when month_cd = 'm-2016-07' then paid_amt else 0 end) as month_7_pmpy,
	sum(case when month_cd = 'm-2016-08' then paid_amt else 0 end) as month_8_pmpy,
	sum(case when month_cd = 'm-2016-09' then paid_amt else 0 end) as month_9_pmpy,
	sum(case when month_cd = 'm-2016-10' then paid_amt else 0 end) as month_10_pmpy,
	sum(case when month_cd = 'm-2016-11' then paid_amt else 0 end) as month_11_pmpy,
	sum(case when month_cd = 'm-2016-12' then paid_amt else 0 end) as month_12_pmpy
from wkbk2.metric_value_qexpu
where org_id = 'A2740' and month_cd between 'm-2016-01' and 'm-2016-12'
group by 1,2),


pat_pmpy as (
select ha.org_id, ha.fk_patient_id,
	sum(pp.month_1_pmpy * ha.month_1_cd + pp.month_2_pmpy * ha.month_2_cd + pp.month_3_pmpy * ha.month_3_cd
		+ pp.month_4_pmpy * ha.month_4_cd + pp.month_5_pmpy * ha.month_5_cd + pp.month_6_pmpy * ha.month_6_cd
		+ pp.month_7_pmpy * ha.month_7_cd + pp.month_8_pmpy * ha.month_8_cd + pp.month_9_pmpy * ha.month_9_cd
		+ pp.month_10_pmpy * ha.month_10_cd + pp.month_11_pmpy * ha.month_11_cd + pp.month_12_pmpy * ha.month_12_cd) as pmpy
from hassgn ha
inner join pat_pmpy_mm pp
on ha.fk_patient_id = pp.fk_patient_id
and ha.org_id = pp.org_id
group by 1,2)

select sum(pmpy) from pat_pmpy