Need help with query... - 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)

Need help with query...

Author

Topic

rpieszak
Starting Member

11 Posts

Posted - 2005-06-10 : 13:26:08

Greetings all,

This is an example of the hourly data that I have...

Timestamp__________Value
3/17/2005 00:00______5
3/17/2005 01:00______7
3/17/2005 02:00______11
...
6/9/2005 21:00_______4
6/9/2005 22:00_______12
6/9/2005 23:00_______15

...but this is the data format that I need...

Timestamp__________Value_______Sum Total
3/17/2005 00:00______5__________5
3/17/2005 01:00______7__________12
3/17/2005 02:00______11_________23
...
6/9/2005 21:00_______4__________29205
6/9/2005 22:00_______12_________29217
6/9/2005 23:00_______15_________29232

The challenge is to be able to do this without having to loop through the records and keep a running total. Is it possible to do this in one query (possibly with subqueries)?

Any questions, let me know.
Thanks in advance for any help
Ryan

mpetanovitch
Yak Posting Veteran

52 Posts

Posted - 2005-06-10 : 13:34:17

[code]
[code]

CREATE TABLE #yak (yak_date datetime, yak_value int)

INSERT INTO #yak
VALUES ('3/17/2005 00:00', 5)
INSERT INTO #yak
VALUES ('3/17/2005 01:00', 7)
INSERT INTO #yak
VALUES ('3/17/2005 02:00', 11)
INSERT INTO #yak
VALUES ('6/9/2005 21:00', 4)
INSERT INTO #yak
VALUES ('6/9/2005 22:00', 12)
INSERT INTO #yak
VALUES ('6/9/2005 23:00', 15)

SELECT y1.yak_date, y1.yak_value, SUM (y2.yak_value) AS yak_sum
FROM #yak y1
INNER JOIN #yak y2 ON y1.yak_date >= y2.yak_date
GROUP BY y1.yak_date, y1.yak_value
ORDER BY y1.yak_date
[/code]
[/code]

Mike Petanovitch

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-13 : 06:15:47

select timestamp,value,(select sum(value)from your_table_name where t.timestamp<=v )as sumtotal from your_table_name t
group by timestamp,value

hope this solves ur problem ......??

Thanks,
Vivek

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-06-13 : 06:16:38

A li'lmodififcation

select timestamp,value,(select sum(value)from your_table_name where t.timestamp<=timestamp )as sumtotal from your_table_name t
group by timestamp,value

wat do ya say ??

Thanks,
Vivek

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources