Sure. It is a long query. The items that are not commented out have been tested by my Galaxy team who enhanced the query to make it run faster. Using the entire year from what you see in the startdt and enddt takes 20 minutes to run. When I add the cert_of_cov_fl_yr and then do any of 7 possible joins about 45mins to an hour and 15mins later it will say disk system full. So I changed my query to run just for 1 day. Using any of the 7 possible joins to the table that houses cert_of_cov_fl_yr, it runs about 3 hours on any of them and returns 695 rows. There are no other possible joins listed in our data dictionary. So I am sure since it takes 3 hours to return 695 rows that account for 1 day, trying to pull the whole year it just cannot handle because 365 days worth of data multiply by 3 hours would be a total runtime of 1080 hours. So not sure what the deal is with that one particular table. And the sad thing is I cannot take that field out because the PM needs it. I can also not just remove it for now and run the one that does run for 20minutes then do a group by the clm_aud_nbr and mbr_sys_id and do a left join to the contract plan coverage table because I am connecting via a DB2 and we cannot take data we extract onto our local machine and link it back to the server in SAS. I can do it in Access and have attempted but it is too large for Access to handle and Access will bomb.
options compress=yes reuse=yes;
%let pass='Year2012';
%let startdt='06/01/2011';
%let enddt='05/31/2011';
%let hgh=('80428','80429','80430','83003','84305','86277','J2940','J2941','S9558','Q0515');
proc sql;
connect to db2(database=dsn user=username password=&pass);
create table hgh.claims as
select * from connection to db2
(SELECT
- a.clm_aud_nbr,
mbr_sys_id,
fst_srvc_dt,
sbscr_nbr,
policy_nbr,
mbr_lst_nm,
mbr_fst_nm,
adj_hccc_cd,
prov_tin,
proc_cd,
diag_1_cd,
diag_2_cd,
diag_3_cd,
diag_4_cd,
mpin,
full_nm,
prdct_cd,
company_cd,
grp_cust_nbr,
er_grp_id,
ben_pln_nbr,
cert_of_cov_fl_yr,
er_grp_id,
grp_cust_nbr,
ben_pln_nbr,
ben_strct_1_cd,
ben_strct_2_cd,
ben_cov_typ_cd,
mbr_cov_mo_row_eff_dt,
mbr_cov_mo_row_end_dt,
finc_arng_cd,
finc_arng_desc,
ama_pl_of_srvc_cd,
ama_pl_of_srvc_desc,
orig_srvc_cd,
member_mkt,
provider_mkt,
bil_typ_cd,
alloc_rvnu_cd,
fm_typ_cd,
fm_typ_desc,
sum(allw_amt) as alloc_allw_amt,
sum(elig_amt) as alloc_elig_amt,
sum(net_pd_amt) as alloc_net_pd_amt,
sum(sbmt_chrg_amt) as alloc_sbmt_chrg_amt
FROM
(
SELECT
DISTINCT
unet.clm_aud_nbr
FROM
galaxy.unet_claim_statistical_service unet
INNER JOIN
galaxy.unet_claim_header head
ON
unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id AND
unet.mbr_sys_id = head.mbr_sys_id
INNER JOIN
galaxy.company_code co
ON head.co_cd = co.co_cd
INNER JOIN
galaxy.product prod
ON head.prdct_cd = prod.prdct_cd
INNER JOIN
galaxy.business_line_code blc
ON prod.bus_line_cd = blc.bus_line_cd
INNER JOIN
galaxy.member_coverage_month mem
ON
unet.mbr_sys_id = mem.mbr_sys_id AND
unet.fst_srvc_dt BETWEEN mem.mbr_cov_mo_row_eff_dt AND mem.mbr_cov_mo_row_end_dt
WHERE
co.finc_arng_cd IN ('F', 'A') AND
mem.cob_ind = ('N') AND
blc.bus_line_desc = 'COMMERCIAL' AND
unet.fst_srvc_dt BETWEEN &startdt and &enddt AND
unet.chrg_sts_cd IN ('P') AND
unet.proc_cd IN &hgh and
unet.allw_amt >0
order by 1
)
b
INNER JOIN
(SELECT
unet.clm_aud_nbr,
unet.mbr_sys_id,
unet.sbscr_nbr,
head.co_cd as company_cd,
head.mbr_lst_nm,
head.mbr_fst_nm,
mem.grp_cust_nbr,
head.ben_strct_1_cd,
head.ben_strct_2_cd,
head.ben_cov_typ_cd,
unet.cust_seg_nbr AS policy_nbr,
unet.fst_srvc_dt,
unet.adj_hccc_cd,
unet.prov_tin,
unet.proc_cd,
unet.diag_1_cd,
unet.diag_2_cd,
unet.diag_3_cd,
unet.diag_4_cd,
prov.mpin,
prov.full_nm,
co.finc_arng_cd,
co.finc_arng_desc,
head.prdct_cd,
plc.ama_pl_of_srvc_cd,
plc.ama_pl_of_srvc_desc,
market.maj_mkt_nm AS member_mkt,
market2.maj_mkt_nm AS provider_mkt,
unet.orig_srvc_cd,
bill.bil_typ_cd,
mem.mbr_cov_mo_row_eff_dt,
mem.mbr_cov_mo_row_end_dt,
unet.rvnu_cd as alloc_rvnu_cd,
mem2.er_grp_id,
mem3.ben_pln_nbr,
cert.cert_of_cov_fl_yr,
head.fm_typ_cd,
frm.fm_typ_desc,
allw_amt,
elig_amt,
net_pd_amt,
sbmt_chrg_amt
FROM
galaxy.unet_claim_statistical_service unet
INNER JOIN
galaxy.unet_claim_header head
ON
unet.unet_clm_head_sys_id = head.unet_clm_head_sys_id AND
unet.mbr_sys_id = head.mbr_sys_id
INNER JOIN
galaxy.provider prov
ON
unet.srvc_prov_row_eff_dt = prov.prov_row_eff_dt AND
unet.srvc_prov_sys_id = prov.prov_sys_id
INNER JOIN
galaxy.company_code co
ON head.co_cd = co.co_cd
inner join galaxy.product prod
ON head.prdct_cd = prod.prdct_cd
INNER JOIN
galaxy.place_of_service_code plc
ON unet.pl_of_srvc_sys_id = plc.pl_of_srvc_sys_id
INNER JOIN
galaxy.unet_market market
ON
head.geo_area_nbr = market.mkt_nbr AND
head.geo_area_nbr_mkt_row_eff_dt = market.mkt_row_eff_dt
inner join galaxy.UNET_FACILITY_UB92 ub92
on head.mbr_sys_id=ub92.mbr_sys_id
and head.UNET_CLM_HEAD_SYS_ID =ub92.UNET_CLM_HEAD_SYS_ID
INNER JOIN
galaxy.cosmos_bill_type_code bill
ON ub92.bil_typ_cd = bill.bil_typ_cd
INNER JOIN
galaxy.unet_market market2
ON
unet.prov_contr_mkt_nbr = market2.mkt_nbr AND
unet.prov_contr_mkt_row_eff_dt = market2.mkt_row_eff_dt
inner join galaxy.member_coverage_month mem
on unet.mbr_sys_id = mem.mbr_sys_id and
unet.fst_srvc_dt between mem.mbr_cov_mo_row_eff_dt and mem.mbr_cov_mo_row_end_dt
inner join galaxy.member mem2
on head.mbr_sys_id = mem2.mbr_sys_id
inner join galaxy.member_coverage mem3
on mem2.mbr_sys_id = mem3.mbr_sys_id
/*inner join galaxy.customer_segment cust
on unet.cust_seg_sys_id = cust.cust_seg_sys_id
and unet.cust_seg_row_eff_dt = cust.cust_seg_row_eff_dt
inner join galaxy.parent_customer parent
on cust.par_cust_nbr = parent.par_cust_nbr
and cust.src_sys_cd = parent.src_sys_cd
INNER JOIN galaxy.contract_plan_coverage cert
on parent.par_cust_sys_id = cert.par_cust_sys_id*/ /*first poss if using cust to parent*/
inner join galaxy.contract_plan_coverage cert
on prod.prdct_cd = cert.prdct_cd
and prod.src_sys_cd = cert.src_sys_cd
inner join galaxy.form_type_code frm
on head.fm_typ_cd = frm.fm_typ_cd
) a
ON a.clm_aud_nbr = b.clm_aud_nbr
group by
- a.clm_aud_nbr,
mbr_sys_id,
sbscr_nbr,
policy_nbr,
mbr_lst_nm,
mbr_fst_nm,
fst_srvc_dt,
adj_hccc_cd,
prov_tin,
proc_cd,
diag_1_cd,
diag_2_cd,
diag_3_cd,
diag_4_cd,
mpin,
full_nm,
prdct_cd,
company_cd,
ben_strct_1_cd,
ben_strct_2_cd,
ben_cov_typ_cd,
mbr_cov_mo_row_eff_dt,
mbr_cov_mo_row_end_dt,
finc_arng_cd,
finc_arng_desc,
ama_pl_of_srvc_cd,
ama_pl_of_srvc_desc,
orig_srvc_cd,
member_mkt,
provider_mkt,
bil_typ_cd,
alloc_rvnu_cd,
er_grp_id,
grp_cust_nbr,
ben_pln_nbr,
cert_of_cov_fl_yr,
fm_typ_cd,
fm_typ_desc)
order by clm_aud_nbr;
disconnect from db2;
quit;
