September 8, 2010 at 11:46 pm
I have a query which brings the data using a view. I want to perform a cumulative sum of the amount column in my query, which is based upon the week no.
The result set of the query is:
Week_no, customer, product, amount, tax.
After the column amount I want to make cumulative sum of the amount and tax columns, until the week no changes. I was able to do the same by using cursor, but I want the results in GRID as I have to return the result to an excel file for making the pivot reports on the same.
Thanks in advance.
Regards,
Pradeep Kumar
September 9, 2010 at 12:33 am
Can you please write a small script that creates the table with the columns and insert few records into it and then show the requested results? I think that I misunderstood what you want. According to my understanding you want to sum the columns only according to the week number regardless of the customer or product in the records. I have a feeling that this isn't what you wanted.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 9, 2010 at 12:38 am
Looks like a running total problem. Check out the article I reference below in my signature block regrading Running Totals AND the discussion that follows it. Lots of good information and some heated duscussion, but well worth the read.
September 9, 2010 at 4:31 am
Thanks Adi and Lynn. Yes as per Lynn my requirement was to have the running total of the amount column.
I had already tried the cursor part of the same and I was able to get the solution.
I tried the triangular join scenario, but I'm not able to get the desired result.
The sample data is as given below
282010-07-09 1007000008191280.0006626505.3409 - the running total is incorrect
282010-07-09 1007000008458960.0006626505.3409
292010-07-12 10070000091056034.4766459237.5492 - the running total is incorrect
292010-07-13 1007000010420480.00006459237.5492
Waiting for a response on the same.
September 9, 2010 at 5:57 am
pradeep.kumar.jakhar (9/9/2010)
Waiting for a response on the same.
The cursor and triangular join are the worst performing options.
If you help us to help you, you'll get the response you are after faster.
Read this for guidance: http://www.sqlservercentral.com/articles/Best+Practices/61537/
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 16, 2010 at 10:35 pm
Dear Paul,
I apologize for the mistake I had made. I will take this in consideration for my future posts.
September 16, 2010 at 11:44 pm
I am not sure about the performance of this solution,but hope this may help you.
The data supplied by you does not contain a unique id.This works only if there such an id exists.
declare @Tmp table(id int,weekno varchar(25),customer varchar(45),product varchar(35),
amount float,tax float)
insert into @Tmp values(1,'28 2010-07-09','A','X',12345.25,90)
insert into @Tmp values(2,'28 2010-07-09','A','y',1245,9)
insert into @Tmp values(3,'29 2010-07-12','S','z',125.098,0)
insert into @Tmp values(4,'29 2010-07-13','S','X',12345,90)
select T.*,(select sum(amount)from @Tmp T1 where T1.id<=T.id
and left(T.weekno,2)=left(t1.weekno,2)
)RunningTotal
from @Tmp T
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply