Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Analytics windowing wuth lag/lead?

Analytics windowing wuth lag/lead?

From: amonte <ax.mount_at_gmail.com>
Date: Thu, 13 Sep 2007 11:33:58 +0200
Message-ID: <85c1fb130709130233y41addaf9yfd28798ef95aaba1@mail.gmail.com>


Hi all

I have a query which returns following results

SELECT
 COD_EJER,
 ID_COMP,
 VAL,
 VERSIONING
FROM COMP_CL
WHERE COD_EJER IN (200202, 200203)
  AND ID_COMP = 1173
  AND KEY = 56     COD_EJER ID_COMP VAL VERSIONING
------------ ---------- ---------- -----------

      200202       1173 22000                1
      200202       1173 23000                2
      200203       1173 30000                1
      200203       1173 30000                3

And teh requirement is:

VAL OF MAX(VERSIONING) of 200203 - VAL OF MAX(VERSIONING) of 200202

So it would return folloging

    COD_EJER ID_COMP VAL VERSIONING DIFF
------------ ---------- ---------- ----------- -----

      200203       1173 30000                3   7000

I know I can do first a MAX(VERSIONING) then filter the max version rows and use lag to subtract but I wonder if there are any better ways such as using windowing?

I am basically treating quarters (200201, 200202, 200203 200204)

Cheers
Alex

--

http://www.freelists.org/webpage/oracle-l Received on Thu Sep 13 2007 - 04:33:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US