special running sum - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

special running sum

Author

Topic

ach.syam
Starting Member

1 Post

Posted - 2007-03-26 : 05:14:28

i need running sum method like this
my input's table is
station time rh
mlg 03/01/07 01:00 2
mlg 03/01/07 02:00 0
mlg 03/01/07 03:00 3
mlg 03/01/07 04:00 0
mlg 03/01/07 05:00 0
mlg 03/01/07 06:00 0
mlg 03/01/07 07:00 0
mlg 03/01/07 08:00 0
sby 03/01/07 01:00 0
sby 03/01/07 02:00 3
sby 03/01/07 03:00 4
sby 03/01/07 04:00 0
sby 03/01/07 05:00 0
sby 03/01/07 06:00 0
sby 03/01/07 07:00 2
sby 03/01/07 08:00 0
sby 03/01/07 09:00 0
sby 03/01/07 10:00 0
sby 03/01/07 11:00 0
sby 03/01/07 12:00 0

ouput's table like this
station time rh cummulative
mlg 03/01/07 01:00 2 2
mlg 03/01/07 02:00 0 2
mlg 03/01/07 03:00 3 5
mlg 03/01/07 04:00 0 5
mlg 03/01/07 05:00 0 5
mlg 03/01/07 06:00 0 5
mlg 03/01/07 07:00 0 5
mlg 03/01/07 08:00 0 0
sby 03/01/07 01:00 0 0
sby 03/01/07 02:00 3 3
sby 03/01/07 03:00 4 7
sby 03/01/07 04:00 0 7
sby 03/01/07 05:00 0 7
sby 03/01/07 06:00 0 7
sby 03/01/07 07:00 2 9
sby 03/01/07 08:00 0 0
sby 03/01/07 09:00 0 0
sby 03/01/07 10:00 0 0
sby 03/01/07 11:00 0 0
sby 03/01/07 12:00 0 0

since 5 hours during start rh<>0, the running sum method calculate the value

thanks for your help
best regards

syam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 05:21:05

You mean this?

station time rh cummulative
mlg 03/01/07 01:00 2 2
mlg 03/01/07 02:00 0 2
mlg 03/01/07 03:00 3 5
mlg 03/01/07 04:00 0 5
mlg 03/01/07 05:00 0 5
mlg 03/01/07 06:00 0 5
mlg 03/01/07 07:00 0 5
mlg 03/01/07 08:00 0 0
sby 03/01/07 01:00 0 0
sby 03/01/07 02:00 3 3
sby 03/01/07 03:00 4 7
sby 03/01/07 04:00 0 7
sby 03/01/07 05:00 0 7
sby 03/01/07 06:00 0 7
sby 03/01/07 07:00 2 9
sby 03/01/07 08:00 0 9
sby 03/01/07 09:00 0 9
sby 03/01/07 10:00 0 9
sby 03/01/07 11:00 0 9
sby 03/01/07 12:00 0 0

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-03-26 : 08:51:29

also, what about this one?

mlg 03/01/07 08:00 0 0

shouldn't the running total be 5 for that line?

Is there data only for one day? Any other conditions that reset the running total that we should be aware of?

Finally, if you don't need this data to be stored in SQL for further calculations, it is very easy and much more efficient to do running sums at the presentation layer rather than in T-SQL , at least before SQL 2005. how are you outputting these results? Report? Web page? etc

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources