CREATE OR REPLACE FORCE VIEW "OPPS_PRS"."PARTICIPATION_PLEDGE_VW" ("PRTCPTN_PLEDGE_ID", "STATE_CD", "PROVIDER_ID", "PRT CPTN_CLNDR_YR", "DT_PRTCPTN", "DT_NON_PRTCPTN", "PRTCPTN_NOTE", "CREAT_USER_ID", "CREAT_DT", "UPDT_USER_ID", "UPDT_DT") A S select t.prtcptn_pledge_id, t.state_cd, t.provider_id, t.prtcptn_clndr_yr, t.dt_prtcptn, t.dt_non_prtcptn, t.prtcptn_note, t.creat_user_id, t.creat_dt, t.updt_user_id, t.updt_dt from opps_import.participation_pledge@QNOLPR01.WORLD@OPPS_IMPORT_USER t CREATE MATERIALIZED VIEW "OPPS_PRS"."HOP_QDRP_MEASURE_COUNTS_MV" ("STATE_CD", "HSP_ID", "QTR_CY", "AMI_TOT_ABSTRCTION_C NT", "AMI_TOT_ABS_CNT_MNTH_1", "AMI_TOT_ABS_CNT_MNTH_2", "AMI_TOT_ABS_CNT_MNTH_3", "CP_TOT_ABSTRCTION_CNT", "CP_TOT_ABS_C NT_MNTH_1", "CP_TOT_ABS_CNT_MNTH_2", "CP_TOT_ABS_CNT_MNTH_3", "SURG_TOT_ABSTRCTION_CNT", "SURG_TOT_ABS_CNT_MNTH_1", "SURG _TOT_ABS_CNT_MNTH_2", "SURG_TOT_ABS_CNT_MNTH_3", "ED_TOT_ABSTRCTION_CNT", "ED_TOT_ABS_CNT_MNTH_1", "ED_TOT_ABS_CNT_MNTH_2 ", "ED_TOT_ABS_CNT_MNTH_3", "PAIN_TOT_ABSTRCTION_CNT", "PAIN_TOT_ABS_CNT_MNTH_1", "PAIN_TOT_ABS_CNT_MNTH_2", "PAIN_TOT_AB S_CNT_MNTH_3", "STROKE_TOT_ABSTRCTION_CNT", "STROKE_TOT_ABS_CNT_MNTH_1", "STROKE_TOT_ABS_CNT_MNTH_2", "STROKE_TOT_ABS_CNT _MNTH_3", "MDCR_CLMS_CNT_AMI", "MDCR_CLMS_CNT_MNTH_1_AMI", "MDCR_CLMS_CNT_MNTH_2_AMI", "MDCR_CLMS_CNT_MNTH_3_AMI", "MDCR_ CLMS_CNT_CP", "MDCR_CLMS_CNT_MNTH_1_CP", "MDCR_CLMS_CNT_MNTH_2_CP", "MDCR_CLMS_CNT_MNTH_3_CP", "MDCR_CLMS_CNT_SURG", "MDC R_CLMS_CNT_MNTH_1_SURG", "MDCR_CLMS_CNT_MNTH_2_SURG", "MDCR_CLMS_CNT_MNTH_3_SURG", "MDCR_CLMS_CNT_ED", "MDCR_CLMS_CNT_MNT H_1_ED", "MDCR_CLMS_CNT_MNTH_2_ED", "MDCR_CLMS_CNT_MNTH_3_ED", "MDCR_CLMS_CNT_PAIN", "MDCR_CLMS_CNT_MNTH_1_PAIN", "MDCR_C LMS_CNT_MNTH_2_PAIN", "MDCR_CLMS_CNT_MNTH_3_PAIN", "MDCR_CLMS_CNT_STROKE", "MDCR_CLMS_CNT_MNTH_1_STROKE", "MDCR_CLMS_CNT_ MNTH_2_STROKE", "MDCR_CLMS_CNT_MNTH_3_STROKE", "OPN_CLSD", "HOP_QDRP_ELIG_INDCTR", "RUN_DT") ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 4194304 NEXT 4194304 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "OPPS_PRS_DATA_16K" BUILD IMMEDIATE USING INDEX REFRESH FORCE ON DEMAND USING DEFAULT LOCAL ROLLBACK SEGMENT USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE AS SELECT p.state_cd ,p.hsp_id ,p.qtr_cy ,a.ami_tot_abstrction_cnt ,amc.qtr_month1_abs_ami ami_tot_abs_cnt_mnth_1 ,amc.qtr_month2_abs_ami ami_tot_abs_cnt_mnth_2 ,amc.qtr_month3_abs_ami ami_tot_abs_cnt_mnth_3 ,a.cp_tot_abstrction_cnt ,amc.qtr_month1_abs_cp cp_tot_abs_cnt_mnth_1 ,amc.qtr_month2_abs_cp cp_tot_abs_cnt_mnth_2 ,amc.qtr_month3_abs_cp cp_tot_abs_cnt_mnth_3 ,a.surg_tot_abstrction_cnt ,amc.qtr_month1_abs_surg surg_tot_abs_cnt_mnth_1 ,amc.qtr_month2_abs_surg surg_tot_abs_cnt_mnth_2 ,amc.qtr_month3_abs_surg surg_tot_abs_cnt_mnth_3 ,a.ed_tot_abstrction_cnt ,amc.qtr_month1_abs_ed ed_tot_abs_cnt_mnth_1 ,amc.qtr_month2_abs_ed ed_tot_abs_cnt_mnth_2 ,amc.qtr_month3_abs_ed ed_tot_abs_cnt_mnth_3 ,a.pain_tot_abstrction_cnt ,amc.qtr_month1_abs_pain pain_tot_abs_cnt_mnth_1 ,amc.qtr_month2_abs_pain pain_tot_abs_cnt_mnth_2 ,amc.qtr_month3_abs_pain pain_tot_abs_cnt_mnth_3 ,a.stroke_tot_abstrction_cnt ,amc.qtr_month1_abs_stroke stroke_tot_abs_cnt_mnth_1 ,amc.qtr_month2_abs_stroke stroke_tot_abs_cnt_mnth_2 ,amc.qtr_month3_abs_stroke stroke_tot_abs_cnt_mnth_3 ,m.mdcr_clms_cnt_ami ,mmc.qtr_month1_ami mdcr_clms_cnt_mnth_1_ami ,mmc.qtr_month2_ami mdcr_clms_cnt_mnth_2_ami ,mmc.qtr_month3_ami mdcr_clms_cnt_mnth_3_ami ,m.mdcr_clms_cnt_cp ,mmc.qtr_month1_cp mdcr_clms_cnt_mnth_1_cp ,mmc.qtr_month2_cp mdcr_clms_cnt_mnth_2_cp ,mmc.qtr_month3_cp mdcr_clms_cnt_mnth_3_cp ,m.mdcr_clms_cnt_surg ,mmc.qtr_month1_surg mdcr_clms_cnt_mnth_1_surg ,mmc.qtr_month2_surg mdcr_clms_cnt_mnth_2_surg ,mmc.qtr_month3_surg mdcr_clms_cnt_mnth_3_surg ,m.mdcr_clms_cnt_ed ,mmc.qtr_month1_ed mdcr_clms_cnt_mnth_1_ed ,mmc.qtr_month2_ed mdcr_clms_cnt_mnth_2_ed ,mmc.qtr_month3_ed mdcr_clms_cnt_mnth_3_ed ,m.mdcr_clms_cnt_pain ,mmc.qtr_month1_pain mdcr_clms_cnt_mnth_1_pain ,mmc.qtr_month2_pain mdcr_clms_cnt_mnth_2_pain ,mmc.qtr_month3_pain mdcr_clms_cnt_mnth_3_pain ,m.mdcr_clms_cnt_stroke ,mmc.qtr_month1_stroke mdcr_clms_cnt_mnth_1_stroke ,mmc.qtr_month2_stroke mdcr_clms_cnt_mnth_2_stroke ,mmc.qtr_month3_stroke mdcr_clms_cnt_mnth_3_stroke --p.actv_pldg, --MC 07/28/2009, RCC 2 Removed ,(CASE WHEN nvl(h.hsp_facility_close_dt, to_date('12/31/9999', 'MM/DD/YYYY')) <= p.qtr_end_dt THEN 'C' WHEN nvl(h.hsp_medicare_accpt_dt, to_date('12/31/9999', 'MM/DD/YYYY')) <= p.qtr_end_dt THEN 'O' ELSE NULL END) AS opn_clsd ,h.hop_qdrp_eligible_ind AS hop_qdrp_elig_indctr -- CAST(NULL AS VARCHAR2(32)) AS qlfyd_abstrction_cnt, --placeholder for future use -- CAST(NULL AS VARCHAR2(32)) AS case_cnt_subddln, --placeholder for future use -- CAST(NULL AS VARCHAR2(32)) AS qlfyd_cnt_subddln, --placeholder for future use ,SYSDATE AS run_dt FROM --Driving Set, alias p, every provider in participation_pledge joined to time_period_quarter and actv_pldg decoded based on dates (SELECT hsp_id ,state_cd ,MAX(actv_pldg) AS actv_pldg --group and select MAX(actv_pldg) to cover instances of multiple pledges from the same hsp_id ,qtr_cy --this combines identical pledges and selects the 'Y' actv_pldg row for a quarter with 'Y' and 'N' ,time_prd_qtr_id --due to multiple pledges and the join ,qtr_dschrg_strt_dt AS qtr_strt_dt ,qtr_dschrg_end_dt AS qtr_end_dt FROM (SELECT dpp.provider_id AS hsp_id ,dpp.state_cd AS state_cd ,(CASE WHEN dpp.dt_prtcptn <= tpq.qtr_dschrg_end_dt AND nvl(dpp.dt_non_prtcptn, to_date('12/31/9999', 'MM/DD/YYYY')) >= tpq.qtr_dschrg_st rt_dt THEN 'Y' ELSE 'N' END) AS actv_pldg ,tpq.qtr_desc AS qtr_cy ,tpq.time_prd_qtr_id ,tpq.qtr_dschrg_strt_dt ,tpq.qtr_dschrg_end_dt FROM opps_prs.syn_time_period_qtr_oi_qnol tpq ,(SELECT provider_id ,state_cd ,dt_prtcptn ,dt_non_prtcptn FROM opps_prs.syn_participation_pledge_opa GROUP BY provider_id ,state_cd ,dt_prtcptn ,dt_non_prtcptn) dpp) GROUP BY hsp_id ,state_cd ,qtr_cy ,time_prd_qtr_id ,qtr_dschrg_strt_dt ,qtr_dschrg_end_dt) p --pivoted abstraction counts, alias a, left outer join the driving set to this ,(SELECT provider_id ,time_prd_qtr_id ,MAX(decode(msr_set_id, 12, cnt, NULL)) ami_tot_abstrction_cnt ,MAX(decode(msr_set_id, 13, cnt, NULL)) cp_tot_abstrction_cnt ,MAX(decode(msr_set_id, 14, cnt, NULL)) surg_tot_abstrction_cnt -- New Measures ,MAX(decode(msr_set_id, 15, cnt, NULL)) ed_tot_abstrction_cnt ,MAX(decode(msr_set_id, 16, cnt, NULL)) pain_tot_abstrction_cnt ,MAX(decode(msr_set_id, 17, cnt, NULL)) stroke_tot_abstrction_cnt FROM (SELECT provider_id ,time_prd_qtr_id ,msr_set_id ,COUNT(*) cnt FROM opps_prs.syn_abstraction GROUP BY provider_id ,time_prd_qtr_id ,msr_set_id) GROUP BY provider_id ,time_prd_qtr_id) a --pivoted monthly abstraction counts, alias amc, left outer join the driving set to this ,(SELECT provider_id ,time_prd_qtr_id ,qtr_month1_abs_ami ,qtr_month2_abs_ami ,qtr_month3_abs_ami ,qtr_month1_abs_cp ,qtr_month2_abs_cp ,qtr_month3_abs_cp ,qtr_month1_abs_surg ,qtr_month2_abs_surg ,qtr_month3_abs_surg -- New Measures ,qtr_month1_abs_ed ,qtr_month2_abs_ed ,qtr_month3_abs_ed ,qtr_month1_abs_pain ,qtr_month2_abs_pain ,qtr_month3_abs_pain ,qtr_month1_abs_stroke ,qtr_month2_abs_stroke ,qtr_month3_abs_stroke FROM (SELECT provider_id, prd_mo, time_prd_qtr_id, lead(ami_tot_abstrction_cnt, 0) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pro vider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_abs_ami, lead(ami_tot_abstrction_cnt, 1) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pro vider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_abs_ami, lead(ami_tot_abstrction_cnt, 2) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pro vider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_abs_ami, lead(cp_tot_abstrction_cnt, 0) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY prov ider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_abs_cp, lead(cp_tot_abstrction_cnt, 1) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY prov ider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_abs_cp, lead(cp_tot_abstrction_cnt, 2) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY prov ider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_abs_cp, lead(surg_tot_abstrction_cnt, 0) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pr ovider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_abs_surg, lead(surg_tot_abstrction_cnt, 1) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pr ovider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_abs_surg, lead(surg_tot_abstrction_cnt, 2) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pr ovider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_abs_surg, -- New Measures lead(ed_tot_abstrction_cnt, 0) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY prov ider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_abs_ed, lead(ed_tot_abstrction_cnt, 1) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY prov ider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_abs_ed, lead(ed_tot_abstrction_cnt, 2) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY prov ider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_abs_ed, lead(pain_tot_abstrction_cnt, 0) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pr ovider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_abs_pain, lead(pain_tot_abstrction_cnt, 1) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pr ovider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_abs_pain, lead(pain_tot_abstrction_cnt, 2) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY pr ovider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_abs_pain, lead(stroke_tot_abstrction_cnt, 0) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY provider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_abs_stroke, lead(stroke_tot_abstrction_cnt, 1) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY provider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_abs_stroke, lead(stroke_tot_abstrction_cnt, 2) over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY provider_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_abs_stroke, row_number() over(PARTITION BY provider_id, time_prd_qtr_id ORDER BY provider_id, time_prd_ qtr_id, to_date(prd_mo, 'Mon')) AS row_num FROM (SELECT provider_id ,time_prd_qtr_id ,prd_mo ,MAX(decode(msr_set_id, 12, cnt, NULL)) ami_tot_abstrction_cnt ,MAX(decode(msr_set_id, 13, cnt, NULL)) cp_tot_abstrction_cnt ,MAX(decode(msr_set_id, 14, cnt, NULL)) surg_tot_abstrction_cnt -- New Measures ,MAX(decode(msr_set_id, 15, cnt, NULL)) ed_tot_abstrction_cnt ,MAX(decode(msr_set_id, 16, cnt, NULL)) pain_tot_abstrction_cnt ,MAX(decode(msr_set_id, 17, cnt, NULL)) stroke_tot_abstrction_cnt FROM (SELECT provider_id ,to_char(to_date(prd_mo, 'mm/yyyy'), 'Mon') AS prd_mo ,time_prd_qtr_id ,msr_set_id ,cnt FROM (SELECT provider_id ,to_char(enctr_dt, 'Mon RRRR') AS prd_mo ,time_prd_qtr_id ,msr_set_id ,COUNT(*) cnt FROM opps_prs.syn_abstraction GROUP BY provider_id ,time_prd_qtr_id ,to_char(enctr_dt, 'Mon RRRR') ,msr_set_id ORDER BY time_prd_qtr_id ,msr_set_id ,to_date(to_char(enctr_dt, 'Mon RRRR'), 'MM/YY/RRRR') ,provider_id)) mcts GROUP BY provider_id ,prd_mo ,time_prd_qtr_id ORDER BY provider_id ,time_prd_qtr_id ,to_date(prd_mo, 'Mon') -- )) WHERE row_num = 1) amc --pivoted claims counts, alias m, left outer join the driving set to this ,(SELECT hosp_id ,time_prd_qtr_id ,MAX(decode(msr_set_id, 12, num_of_clms, NULL)) mdcr_clms_cnt_ami ,MAX(decode(msr_set_id, 13, num_of_clms, NULL)) mdcr_clms_cnt_cp ,MAX(decode(msr_set_id, 14, num_of_clms, NULL)) mdcr_clms_cnt_surg -- New Measures ,MAX(decode(msr_set_id, 15, num_of_clms, NULL)) mdcr_clms_cnt_ed ,MAX(decode(msr_set_id, 16, num_of_clms, NULL)) mdcr_clms_cnt_pain ,MAX(decode(msr_set_id, 17, num_of_clms, NULL)) mdcr_clms_cnt_stroke FROM opps_prs.syn_opps_medicare_claims_cnts GROUP BY hosp_id ,time_prd_qtr_id) m --pivoted monthly claims counts, alias mmc, left outer join the driving set to this ,(SELECT hosp_id ,time_prd_qtr_id ,qtr_month1_ami ,qtr_month2_ami ,qtr_month3_ami ,qtr_month1_cp ,qtr_month2_cp ,qtr_month3_cp ,qtr_month1_surg ,qtr_month2_surg ,qtr_month3_surg -- New Measures ,qtr_month1_ed ,qtr_month2_ed ,qtr_month3_ed ,qtr_month1_pain ,qtr_month2_pain ,qtr_month3_pain ,qtr_month1_stroke ,qtr_month2_stroke ,qtr_month3_stroke FROM (SELECT hosp_id, prd_mo, time_prd_qtr_id, lead(mdcr_clms_cnt_ami, 0) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, tim e_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_ami, lead(mdcr_clms_cnt_ami, 1) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, tim e_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_ami, lead(mdcr_clms_cnt_ami, 2) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, tim e_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_ami, lead(mdcr_clms_cnt_cp, 0) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time _prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_cp, lead(mdcr_clms_cnt_cp, 1) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time _prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_cp, lead(mdcr_clms_cnt_cp, 2) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time _prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_cp, lead(mdcr_clms_cnt_surg, 0) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, ti me_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_surg, lead(mdcr_clms_cnt_surg, 1) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, ti me_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_surg, lead(mdcr_clms_cnt_surg, 2) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, ti me_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_surg, -- New Measures lead(mdcr_clms_cnt_ed, 0) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time _prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_ed, lead(mdcr_clms_cnt_ed, 1) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time _prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_ed, lead(mdcr_clms_cnt_ed, 2) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time _prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_ed, lead(mdcr_clms_cnt_pain, 0) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, ti me_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_pain, lead(mdcr_clms_cnt_pain, 1) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, ti me_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_pain, lead(mdcr_clms_cnt_pain, 2) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, ti me_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_pain, lead(mdcr_clms_cnt_stroke, 0) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month1_stroke, lead(mdcr_clms_cnt_stroke, 1) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month2_stroke, lead(mdcr_clms_cnt_stroke, 2) over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS qtr_month3_stroke, row_number() over(PARTITION BY hosp_id, time_prd_qtr_id ORDER BY hosp_id, time_prd_qtr_id, to_date(prd_mo, 'Mon')) AS row_num FROM (SELECT hosp_id ,time_prd_qtr_id ,prd_mo ,MAX(decode(msr_set_id, 12, num_of_clms, NULL)) mdcr_clms_cnt_ami ,MAX(decode(msr_set_id, 13, num_of_clms, NULL)) mdcr_clms_cnt_cp ,MAX(decode(msr_set_id, 14, num_of_clms, NULL)) mdcr_clms_cnt_surg -- New Measures ,MAX(decode(msr_set_id, 15, num_of_clms, NULL)) mdcr_clms_cnt_ed ,MAX(decode(msr_set_id, 16, num_of_clms, NULL)) mdcr_clms_cnt_pain ,MAX(decode(msr_set_id, 17, num_of_clms, NULL)) mdcr_clms_cnt_stroke --FROM haji.opps_medicare_claims_cnts_mnth FROM opps_prs.syn_opps_mdcr_clms_cnts_mnth GROUP BY hosp_id ,prd_mo ,time_prd_qtr_id ORDER BY hosp_id ,time_prd_qtr_id ,to_date(prd_mo, 'Mon') -- )) WHERE row_num = 1) mmc --hlth_serv_provider, alias h, joined with driving set for opn_clsd decode and hop_qdrp_elig_indctr ,np_sdps.hlth_serv_provider h --end data sets WHERE p.hsp_id = a.provider_id(+) AND p.time_prd_qtr_id = a.time_prd_qtr_id(+) AND p.hsp_id = amc.provider_id(+) AND p.time_prd_qtr_id = amc.time_prd_qtr_id(+) AND p.hsp_id = m.hosp_id(+) AND p.time_prd_qtr_id = m.time_prd_qtr_id(+) AND p.hsp_id = mmc.hosp_id(+) AND p.time_prd_qtr_id = mmc.time_prd_qtr_id(+) AND p.hsp_id = h.hsp_id