Apologies for not reading your post thoroughly enough.. :-(
Cheers Connor --- Jack van Zanen <[EMAIL PROTECTED]> wrote: > Didn't they come in with Oracle 8i? > > I could not find anything analytical in the online > documentation (than > again couldn't find the 8.0.5 warehouse guide > either) > > I'm unfortunately on 8.0.5 for this one > > > Jack > > > > > > Connor McDonald > > > <[EMAIL PROTECTED] To: > Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > uk> cc: > (bcc: Jack van Zanen/nlzanen1/External/MEY/NL) > > Sent by: > Subject: Re: Rolling sums (?) > > [EMAIL PROTECTED] > > > > > > > > > 15-08-2002 16:28 > > > Please respond to > > > ORACLE-L > > > > > > > > > > > > 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). > > > > =================================================================== > 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).
