Take a look at the windowing functions under 'ana;ytic functions' in the data warehouse guide...They are very very cool..
hth connor --- Jack van Zanen <[EMAIL PROTECTED]> wrote: > Maybe having a bad hairday but following code will > give me for every period > number the sum of all work in progress for the > period plus the previous 12 > periods. Basically it takes the period number from > the current record and > sums a column of that record with the previous 12 > periods. > > year period value > 2000 1 1 > 2000 2 2 > 2000 3 3 > 2000 4 4 > 2000 5 5 > 2000 6 6 > 2000 7 7 > 2000 8 8 > 2000 9 9 > 2000 10 10 > 2000 11 11 > 2000 12 12 > 2001 1 13 > 2001 2 14 > 2001 3 15 > > So for period 2001 1 I need the values 1 thru 13 > added together (91) > Period 2001 2 needs to be the values 2 thru 14 added > together (104) > etc.... > > As you maybe can imagine the explain plan for this > baby is 4 full > tablescans on quite large tables. (see below) > > My question is if somebody has a better solution to > handle this query????? > > Oracle 8.0.5 !!! > AIX 4.3.3 > > > SELECT V1.ENGAGEMENT > , V1.YEAR > , V1.PERIOD > , MAX(V1.NET_FEE_EARNED_PTD) > , MAX(V1.EXP_WIP_VAL_PTD) > , SUM(V2.TIME_WIP_VAL) > , MAX(V1.GNRL_WON) > , MAX(V1.GNRL_WOFF) > , MAX(V1.TIME_WOFF) > , MAX(V1.WIP_PROV) > , MAX(V1.EXP_WOFF) > , MAX(V1.DB_WOFF) > , MAX(V1.DB_PROV) > FROM > ( > select max(WSTAT.ENG_NUMB) AS ENGAGEMENT, > max(WSTAT.FNANCL_YEAR) AS YEAR, > max(WSTAT.PRD_NUMB) AS PERIOD, > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0)) > + SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0)) > - SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0)) > - SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD, > 0)) > - SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0)) > - SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD, > 0)) > - SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0)) > - SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0)) > NET_FEE_EARNED_PTD, > SUM(NVL(WSTAT.EXP_WIP_VAL_PTD,0)) > EXP_WIP_VAL_PTD, > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0)) > TIME_WIP_VAL, > SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0)) > GNRL_WON, > SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0)) > GNRL_WOFF, > SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD, > 0)) TIME_WOFF, > SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0)) > WIP_PROV, > SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD, 0)) > EXP_WOFF, > SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0)) > DB_WOFF, > SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0)) > DB_PROV > FROM ENG_WIP_STATS WSTAT, > ENG_DBTRS_STATS DSTAT > WHERE WSTAT.ENG_NUMB(+)=DSTAT.ENG_NUMB > AND WSTAT.FNANCL_YEAR=DSTAT.FNANCL_YEAR > AND WSTAT.PRD_NUMB=DSTAT.PRD_NUMB > GROUP BY WSTAT.ENG_NUMB, > WSTAT.FNANCL_YEAR, > WSTAT.PRD_NUMB > ) v1, > ( > select max(WSTAT.ENG_NUMB) AS ENGAGEMENT, > max(WSTAT.FNANCL_YEAR) AS YEAR, > max(WSTAT.PRD_NUMB) AS PERIOD, > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0)) > + SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0)) > - SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0)) > - SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD, > 0)) > - SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0)) > - SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD, > 0)) > - SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0)) > - SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0)) > NET_FEE_EARNED_PTD, > SUM(NVL(WSTAT.EXP_WIP_VAL_PTD,0)) > EXP_WIP_VAL_PTD, > SUM(NVL(WSTAT.TIME_WIP_VAL_PTD, 0)) > TIME_WIP_VAL, > SUM(NVL(WSTAT.GNRL_W_ON_PTD, 0)) > GNRL_WON, > SUM(NVL(WSTAT.GNRL_W_OFF_PTD, 0)) > GNRL_WOFF, > SUM(NVL(WSTAT.TIME_WIP_VAL_W_OFF_PTD, > 0)) TIME_WOFF, > SUM(NVL(WSTAT.WIP_PRVSN_PTD, 0)) > WIP_PROV, > SUM(NVL(WSTAT.EXP_WIP_VAL_W_OFF_PTD, 0)) > EXP_WOFF, > SUM(NVL(DSTAT.DBTRS_W_OFF_PTD, 0)) > DB_WOFF, > SUM(NVL(DSTAT.DBTRS_PRVSN_PTD, 0)) > DB_PROV > FROM ENG_WIP_STATS WSTAT, > ENG_DBTRS_STATS DSTAT > WHERE WSTAT.ENG_NUMB(+)=DSTAT.ENG_NUMB > AND WSTAT.FNANCL_YEAR=DSTAT.FNANCL_YEAR > AND WSTAT.PRD_NUMB=DSTAT.PRD_NUMB > GROUP BY WSTAT.ENG_NUMB, > WSTAT.FNANCL_YEAR, > WSTAT.PRD_NUMB > ) V2 > WHERE v1.ENGAGEMENT=V2.ENGAGEMENT(+) > AND TO_DATE(V2.YEAR(+)||V2.PERIOD(+),'YYYYMM') >= > ADD_MONTHS(TO_DATE(V1.YEAR||V1.PERIOD,'YYYYMM'),-12) > AND TO_DATE(V2.YEAR(+)||V2.PERIOD(+),'YYYYMM') <= > TO_DATE(V1.YEAR||V1.PERIOD,'YYYYMM') > GROUP BY V1.ENGAGEMENT > , V1.YEAR > , V1.PERIOD > > > > Explain plan: > > ID PID QUERY_PLAN > ----- ----- > ------------------------------------------------------------------------------------ > 0 SELECT STATEMENT Cost = 12531921 > 1 0 SORT GROUP BY Card=62754915 > 2 1 MERGE JOIN OUTER Card=62754915 > 3 2 SORT JOIN Card=1584360 > 4 3 VIEW Card=1584360 > 5 4 SORT GROUP BY Card=1584360 > 6 5 FILTER Card= > 7 6 MERGE JOIN OUTER Card= > 8 7 SORT JOIN Card=1354977 > 9 8 TABLE ACCESS FULL > ENG_DBTRS_STATS > Card=1354977 > 10 7 SORT JOIN Card=1956365 > 11 10 TABLE ACCESS FULL > ENG_WIP_STATS Card=1956365 > 12 2 FILTER Card= > 13 12 SORT JOIN Card= > 14 13 VIEW Card=1584360 > 15 14 SORT GROUP BY Card=1584360 > 16 15 FILTER Card= > 17 16 MERGE JOIN OUTER Card= > 18 17 SORT JOIN > Card=1354977 > 19 18 TABLE ACCESS FULL > ENG_DBTRS_STATS > Card=1354977 > 20 17 SORT JOIN > Card=1956365 > 21 20 TABLE ACCESS FULL > ENG_WIP_STATS > Card=1956365 > > > THX > > =================================================================== > De informatie verzonden in dit e-mailbericht is > vertrouwelijk en is > uitsluitend bestemd voor de geadresseerde. > Openbaarmaking, > vermenigvuldiging, verspreiding en/of verstrekking > van deze informatie aan > derden is, behoudens voorafgaande schriftelijke > toestemming van Ernst & > Young, niet toegestaan. Ernst & Young staat niet in > voor de juiste en > volledige overbrenging van de inhoud van een > verzonden e-mailbericht, noch > voor tijdige ontvangst daarvan. Ernst & Young kan > niet garanderen dat een > verzonden e-mailbericht vrij is van virussen, noch > dat e-mailberichten > worden overgebracht zonder inbreuk of tussenkomst > van onbevoegde derden. > > Indien bovenstaand e-mailbericht niet aan u is > gericht, verzoeken wij u > vriendelijk doch dringend het e-mailbericht te > retourneren aan de verzender > en het origineel en eventuele kopieën te verwijderen > en te vernietigen. > > Ernst & Young hanteert bij de uitoefening van haar > werkzaamheden algemene > voorwaarden, waarin een beperking van > aansprakelijkheid is opgenomen. De > algemene voorwaarden worden u op verzoek kosteloos > toegezonden. > ===================================================================== > The information contained in this communication is > confidential and is > intended solely for the use of the individual or > entity to whom it is > addressed. You should not copy, disclose or > distribute this communication > without the authority of Ernst & Young. Ernst & > Young is neither liable for > the proper and complete transmission of the > information contained in this > communication nor for any delay in its receipt. > Ernst & Young does not > guarantee that the integrity of this communication > has been maintained nor > that the communication is free of viruses, > interceptions or interference. > > If you are not the intended recipient of this > communication please return > the communication to the sender and delete and > destroy all copies. > > In carrying out its engagements, Ernst & Young > applies general terms and > conditions, which contain a clause that limits its > liability. A copy of > these terms and conditions is available on request > free of charge. > =================================================================== > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Jack van Zanen > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing). ===== Connor McDonald http://www.oracledba.co.uk http://www.oaktable.net "Remember amateurs built the ark - Professionals built the Titanic" __________________________________________________ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Connor=20McDonald?= INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
