| Author |
Topic |
|
miked1978
Starting Member
25 Posts |
 Posted - 2009-01-16 : 09:27:36
|
| Hey guys, Below is an example of a table that I have and I need to calculate the Value field as a running sum. The running sum field is what I need the code to do. My table name step1 and I have about 35,000 records like below. Any help would be greatly appreciated.Hull Date Div Value Running Sum1234 1/1/06 pipe 50 501234 1/8/06 pipe 50 100 1234 1/15/06 pipe 60 1601234 1/22/06 pipe 10 170 9999 10/1/07 paint 5 59999 10/1/07 paint 12 179999 10/1/07 mach 8 8 9999 10/1/07 mach 7 15 |
|
|
ashishashish
Constraint Violating Yak Guru
408 Posts |
Posted - 2009-01-16 : 10:13:09
|
| Please post your Expected Output data as u wish it will be,,,,,,Wat calculation you want on to be Value,provide the output as u want...Thanks.... |
 |
|
|
miked1978
Starting Member
25 Posts |
Posted - 2009-01-16 : 10:23:41
|
quote:
Originally posted by ashishashish
Please post your Expected Output data as u wish it will be,,,,,,Wat calculation you want on to be Value,provide the output as u want...Thanks....
Sorry I forgot to mention but the example table I typed is what I want and the RunningSum column is what I want the code to calculate.I'm thinking about creating a dummy table with all the columns in it and creating a new Date column that subtracts 7 days from the original date column then do a join by matching on the original date to the new calcualted date and do the sum that way. |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2009-01-16 : 10:24:34
|
| [code]declare @tab1 table (hull int, date datetime, div varchar(10), value int)insert into @tab1select 1234,'1/1/06', 'pipe', 50union all select 1234, '1/8/06', 'pipe', 50 union all select 1234, '1/15/06', 'pipe', 60 union all select 1234, '1/22/06', 'pipe', 10 union all select 9999, '10/1/07', 'paint', 5 union all select 9999, '10/1/07', 'paint', 12union all select 9999, '10/1/07', 'mach', 8 union all select 9999, '10/1/07', 'mach', 7 ;with tab1 as (select rowid, hull, date, div, value, runsum from (select row_number() over (partition by hull,div order by hull) as rowid, hull, date, div, value, value as runsum from @tab1)twhere rowid = 1union allselect a.rowid+1, b.hull, b.date, b.div, b.value, a.runsum + b.value from tab1 a inner join (select row_number() over (partition by hull,div order by hull) as rowid, hull, date, div, value, value as runsum from @tab1) bon a.hull = b.hull and a.div = b.div and a.rowid - b.rowid = -1where a.rowid < b.rowid)select * from tab1 order by hull, div, rowid[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-16 : 10:34:28
|
quote:
Originally posted by miked1978
Hey guys, Below is an example of a table that I have and I need to calculate the Value field as a running sum. The running sum field is what I need the code to do. My table name step1 and I have about 35,000 records like below. Any help would be greatly appreciated.Hull Date Div Value Running Sum1234 1/1/06 pipe 50 501234 1/8/06 pipe 50 100 1234 1/15/06 pipe 60 1601234 1/22/06 pipe 10 170 9999 10/1/07 paint 5 59999 10/1/07 paint 12 179999 10/1/07 mach 8 8 9999 10/1/07 mach 7 15
;With CTE (Seq,Hull,Date,Div,Value,[Running Sum])AS(SELECT ROW_NUMBER() OVER (PARTITION BY Hull,Div ORDER BY Date) AS Seq,Hull,Date,Div,Value,0 FROM Table )UPDATE tSET t.[Running Sum]=t.[Value] +COALESCE(tmp.PrevTotal,0)FROM CTE tOUTER APPLY (SELECT SUM([Value]) AS PrevTotal FROM CTE WHERE Hull=t.Hull AND Div=t.Div AND Seq<t.Seq)tmp |
|
|
|
|
|
|
Running Sum