Splitting table in different timestamps

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 2005 Forums

Transact-SQL (2005)

Splitting table in different timestamps

Author

Topic

WtFudgE
Starting Member

2 Posts

Posted - 2009-07-08 : 03:56:39

Hi, I kind of need some help writing some querries, because these ones are a little out of my reach. The thing is, I have this gigantic table with more than 600000 items in it. The columns needed to explain what I want are basicly just an ID and a receivedDate. Now I use this table in a webservice where I show a lot of other columns. However I also use it to show charts and graphs of the data between different timestamps. However it needs to pul a lot of querries if I want to for example show a linegraph of the number each day. So I was thinking of creating a different table which already did most of these calculations.
The thing is, I need the number of items for each hour, each day and each month. However I don't think each day and each month is necesarry since I can just sum up the numbers for the hours then. So my question is, what querry do I need to create a view of this table which shows the count for each different hour? (so if I would have a database of a complete year I would need like more than 8000 rows, but that's not a problem. Just need to know how, can't figure it out.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-08 : 04:00:02

something like this


select hour = dateadd(hour, datediff(hour, 0, datecol), 0), sum(somecol)
from   sometable
group  by dateadd(hour, datediff(hour, 0, datecol), 0)

KH
[spoiler]Time is always against us[/spoiler]

WtFudgE
Starting Member

2 Posts

Posted - 2009-07-08 : 07:06:07

alright, sum needed to be count, but it works!

Thanks mate !!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources