Cumulative differences

[uparna]

Hi ,

i had exactly the same problem in Oracle a few weeks ago. I struggled with it and found the solution in ROW_NUMBER. Oracle being my stronger field , i was able to get a solution. Unfortunately , i have exactly the same problem in MSSQL and i have no clue how to solve it. Moreover , the MSSQL server is not mine, so i cant ask for procedures/views. I have the data , and i can just query it. Request the MSSQL gurus out there to help me out!


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 .

 

[vongrunt]

Yup, ROW_NUMBER would be nice...

You can do SELECT IDENTITY(1, 1), .... INTO #temptable ORDER BY ..., and this will simulate ranking. However, this is really gray zone (ordered insert) and generally not recommended for larger data sets.

Another way:

select fs.flower, fs.month, fs.year,
fs.numbers_sold - 
(	select top 1 Numbers_Sold
	from flower_sale fs2 
	where fs.flower=fs2.flower 
		and  (fs.year > fs2.year or (fs.year=fs2.year and fs.month > fs2.month))
	order by [year], [month] desc
) actual_sales
from flower_sale fs
order by flower, [year], [month]

For better performance index on (Flower, [Year], [Month]) is welcomed.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[uparna]

excellent suggestion. Tried it out, but i think since fs2 is used before fs is defined , it is erring out...

Regards,
S. Jayaram Uparna .

 

[Katy44]

That shouldn't cause a problem, what is the error?

 

[uparna]

[COLOR=red yellow]"Column 'fs.flower' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"
[/color]

thats the error.... ( i am using a group by in the actual case...)

Regards,
S. Jayaram Uparna .

 

[Katy44]

What exactly ae you trying to achieve with the aggregate? Can you post the query exactly as you ran it?

 

[uparna]

actually , the problem is that there is no year or month in the inner query , but we are sorting by it. That is what is causing the problem....how do we get around it ... another subquery?

Regards,
S. Jayaram Uparna .

 

[uparna]

did another subquery and it ran. woo hoo ~!~

whats the equivalent of oracle's NVL in MS ? since not all the flower's have a prev month record , i am getting NULLs in the results....so i want to replace the nulls in the subtraction ( the "lastmonth" part in thismonth-lastmonth equation) with a 0, so that i get a value instead of null...

Regards,
S. Jayaram Uparna .

 

[vongrunt]

ISNULL(), same arguments and semantics as NVL().

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[uparna]

thanks. used it.....and all i am getting is nulls. I have to investigate this further. but, all that apart , i would like to thank vongrunt and katy for your exceptional support. thank you , thank you , thank you!

Regards,
S. Jayaram Uparna .