Grouping by Hour - 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 2005 Forums

Transact-SQL (2005)

Grouping by Hour

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 13:15:30

declare @tbl as table(id int,value int,dt datetime,highvalueavg int)
insert into @tbl
select 1,2,'2008-01-02 12:30:00',1 union all
select 1,5,'2008-01-02 12:40:00',1 union all
select 1,8,'2008-01-02 12:55:00',1 union all
select 1,10,'2008-01-02 13:30:00',1 union all
select 1,12,'2008-01-02 13:40:00',1 union all
select 1,60,'2008-01-02 16:25:00',1 union all
select 1,20,'2008-01-02 16:28:00',1 union all
select 1,12,'2008-01-02 16:28:00',1 union all
select 2,4,'2008-01-02 16:28:00',3 union all
select 2,2,'2008-01-02 17:40:00',3

What I want is that I need to find the sum of value w.r.t highvalueavg which are the no of hours by which I need to group by.
For example for id 1 the sum should be 15 for 1200 hour because it is grouped by one hour as highvalueavg is 1 same for next hour i.e for 1300 it should be 22 & for 1400 the result should be 92.

Now for id 3 the sum should be 6 as the sum of value should be found out for every 3 hours.
I hope I could explain my problem.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-08 : 13:36:05

[code]declare @tbl as table(id int,value int,dt datetime,highvalueavg int)
insert into @tbl
select 1,2,'2008-01-02 12:30:00',1 union all
select 1,5,'2008-01-02 12:40:00',1 union all
select 1,8,'2008-01-02 12:55:00',1 union all
select 1,10,'2008-01-02 13:30:00',1 union all
select 1,12,'2008-01-02 13:40:00',1 union all
select 1,60,'2008-01-02 16:25:00',1 union all
select 1,20,'2008-01-02 16:28:00',1 union all
select 1,12,'2008-01-02 16:28:00',1 union all
select 2,4,'2008-01-02 16:28:00',3 union all
select 2,2,'2008-01-02 17:40:00',3

-- Peso
SELECT ID,
DATEADD(HOUR, DATEDIFF(HOUR, 0, dt) / highvalueavg * highvalueavg, 0),
SUM(value)
FROM @tbl
GROUP BY ID,
DATEADD(HOUR, DATEDIFF(HOUR, 0, dt) / highvalueavg * highvalueavg, 0)
ORDER BY ID,
DATEADD(HOUR, DATEDIFF(HOUR, 0, dt) / highvalueavg * highvalueavg, 0)[/code]

E 12°55'05.63"
N 56°04'39.26"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-08 : 13:41:57

Yes i had found the soln.But it was a little primitive.

select sum(value),id from @tbl
group by datepart(hh,dt)/highvalueavg,id

But Peso's one looks more clean.Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources