August 17, 2015 at 1:33 pm
I need help to create a sequential aggregate of a column using sum in tsql.
I want a column that will sum up all hours work by transaction date.
for example
Monday my hours work will be 8 and therefore my total transaction hours will be 8.
Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to 16.
Wed my total hours work will be 5 and I want the total hours of my total transaction hours to 21. etc.
Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)
from table
Please let me know if I need to explain more.
August 17, 2015 at 1:38 pm
What you are looking for is a running total. Will need more information, DDL for the table (CREATE TABLE statement), sample data (INSERT INTO statements), expected results based on the sample data. Business rules will also be needed to know exactly how the totals are generated.
Need help with all this, read and follow the instruction in the first article referenced below in my signature block.
August 17, 2015 at 1:38 pm
In 2012+ versions, you can use the OVER() clause for this.
In 2008 and previous versions, there are different methods which are fully explained in here along with a performance comparison:
August 17, 2015 at 1:55 pm
Luis Cazares (8/17/2015)
In 2012+ versions, you can use the OVER() clause for this.In 2008 and previous versions, there are different methods which are fully explained in here along with a performance comparison:
Exactly the article I thought of when I saw the subject. 😛
August 17, 2015 at 2:12 pm
Thanks all for your help. But this link here helped me out
August 17, 2015 at 2:29 pm
I hope that you're not using the triangular joins methods as they're very slow. If you want to know the reason, it's explained in here: http://www.sqlservercentral.com/articles/T-SQL/61539/
For the cursor option, you shouldn't keep the default settings. A better option is to define it as LOCAL FORWARD_ONLY.
Don't forget to include the ORDER BY when you define your cursor.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply