jeudi 30 juillet 2015

Running total column with derived starting point

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

Aucun commentaire:

Enregistrer un commentaire