Dear Expers -
I would like to get rid of the "self joins" from the above query or looking for better way to write this query.
act_dim
--------
act_dim grp_typ acct_id grp_id legal_relat_cd eff_dt exp_dt
51555555 A 3777 3777 DISC 01/04/2008 04/29/2008
515555554849 S 3777 377701 DISC 10/18/2007 04/29/2008
515555554855 S 3777 377707 DISC 02/27/2007 04/29/2008
fact_mthly
----------
act_dim wsp_dim_key tot_usd eff_dt
515555554849 515555554849 32223739 03/31/2008
515555554850 515555554850 14769202.46 03/31/2008
I'm having an issue matching summary and detail level reports.
If grp_typ = A then its a Summary level. If its "S" then its a detail level.
one account_id would have multiple GRP_IDs beneath it. Tot_usd is stored in fact_mthly at the grp level so whether the SQL displays at the group (grp_id) level or at the account level the total should be consistent
The Query
SELECT ap.act_dim,
ap.ACCT_ID,
ap.ACCT_LONG_NM,
ap.ACCT_NAME,
ap.ACCT_SYMBOL,
ap.ACCT_TYP,
af.eff_dt FACT_EFF_DT,
ap.EFF_DT,
ap.EXP_DT,
ap.GRP_ID,
ap.GRP_SUBTYP,
ap.GRP_TYP,
ap.LEGAL_RELAT_CD,
agda_part.acct_id part_acct_id,
agda_part.acct_name part_acct_name,
agda_part.product part_product,
SUM(af.TOT_USD) over (partition by agda_part.grp_id,agda_part
.product,a
f.eff_dt, ap.grp_id,ap.product) TOT_USD
FROM act_dim agd_part ,
act_dim agda_part ,
act_dim agd_pool ,
act_dim agda_pool ,
fact_mthly af
WHERE af.act_dim = agd_part.act_dim
AND af.wsp_dim_key = agd_pool.act_dim
AND agd_part.acct_id = agda_part.acct_id
AND agd_pool.acct_id = ap.acct_id
AND af.eff_dt BETWEEN agd_part.eff_dt AND agd_part.exp_dt - 1
AND af.eff_dt BETWEEN agd_pool.eff_dt AND agd_pool.exp_dt - 1
AND af.eff_dt BETWEEN agda_part.eff_dt AND agda_part.exp_dt - 1
AND af.eff_dt BETWEEN ap.eff_dt AND ap.exp_dt - 1
AND agda_part.grp_typ = 'A'
AND ap.grp_typ = 'A'
Start Free Trial
