SQLTeam.com | Weblogs | Forums

T-SQL 2005 Running Total Column with derived starting point

sql2005
tsql

#1

I have a table like below with a primary key of rootID and I want to update the column DaysRunningTotal with a running total of DaysBetween, but ground zero will be the value of rootID which has a HIGHLIGHT value of 1 - and there will only ever be one instance of this. The DaysBetween has been derived from a datetime column So after running an update script the table will look like the second one below. What script will do this please?

rootID	HIGHLIGHT	DaysRunningTotal	DaysBetween
1	0	        NULL	                0
2	1	        NULL	                3
3	0	        NULL	                4
4	0	        NULL	                212
5	2	        NULL	                0
6	0	        NULL	                0
7	0	        NULL	                0
8	0	        NULL	                18
9	0	        NULL	                0
10	0	        NULL	                112


rootID	HIGHLIGHT	DaysRunningTotal	DaysBetween
1	0	        -3	                0
2	1	         0	                3
3	0	         7	                4
4	0	         219	                212
5	2	         219	                0
6	0	         219	                0
7	0	         219	                0
8	0	         237	                18
9	0	         237           	        0
10	0	         349	                112

#2

Fixed this now by revisiting the datetime column and using the datediff function :slight_smile: