Cumulative Differences

[uparna]

Hi ,

Suppose there is a table 'FLOWER_SALE' like this :-

Flower  Year   Month   Numbers_Sold
------------------------------------
Rose     2004    12      1000
Lily     2004    02      500
Rose     2004    11      900
Rose     2004    10      890
Iris     2004    01      200
Lily     2004    01      300

Now, the numbers_sold column is a cumulative field for a particular flower/year combination...for eg,

Rose 2004 dec has 1000
Rose 2004 nov has 900 and
Rose 2004 oct has 890.

So ,
number of roses sold in 2004 dec = 1000 - 900 = 100 and
number of roses sold in 2004 nov = 900 - 890 = 10
and so on...


now i am supposed to create a report which shows the actual sales per year :-

[COLOR=red yellow]sales report[/color]

Flower    Year    Month     Actual_Sales
-----------------------------------------
Rose       2004    12          100
Rose       2004    11          10
.
.
.

I know we can use aggregate functions like row_number and OVER for this , but cant quite land with the proper syntax. Does anyone have an answer for this?

Regards,
S. Jayaram Uparna .

 

[uparna]

by the way i am still in 8i in this db....going to 9i in a couple of months...

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Jay,

One solution (and probably the best solution) comes with "Oracle Analytics". Rather than my coding up a solution for you, you will be more proficient if you read up on that feature and build and test solutions personally.



Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[uparna]

Santa , i agree. would "lag" be the clue?

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

SantaMufasa's First Rule of Computer Heuristics:

One test is worth ten expert opinions

A test will be faster than waiting for my answer on Tek-Tips.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[uparna]

well, i was "testing" before and after posting here....thanks for the encouragement.

got the answer :-

select flower,
       year,
       month,    
       lag(Numbers_Sold, 1) 
          over(partition by flower,year 
          order by flower desc, month desc) NEXT_MTH,
       Numbers_Sold,
       lag(Numbers_Sold, 1) 
          over(partition by flower,year 
          order by flower desc, month desc)-Numbers_Sold THIS_MTH_SALE
  from Flower
 order by flower desc, month desc

thanks santa !

now i have to figure out a way to get over the dec-jan hurdle...

Regards,
S. Jayaram Uparna .

 

[SantaMufasa]

Very well done, Jay ! You are a quick study.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.