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 :-
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 :-
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 .
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 :-
Code:
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 :-
Code:
[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 .