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