Getting sum of values grouped by hours.

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)

Getting sum of values grouped by hours.

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-23 : 09:21:30

Hi guys,

This is in continuation from this post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=129735

Is there a way by which I can get the startdate and enddate of the hour slot?

Thanks in advance.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-24 : 02:17:46

[code]
select datediff(hour, @ref_date, [date]) / @hour_slot,
min([date]),
max([date]),
sum([value])
from @foo
group by datediff(hour, @ref_date, [date]) / @hour_slot
[/code]

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-24 : 02:42:00

Sorry khtan it aint working


DECLARE @foo TABLE (
	[date] DATETIME
	, [value] MONEY
	)

INSERT @foo ([date], [value])
      SELECT '2009-01-20 07:00:00.000', 0
UNION SELECT '2009-01-20 08:00:00.000', 3104.04
UNION SELECT '2009-01-20 09:00:00.000', 3022.31
UNION SELECT '2009-01-20 10:00:00.000', 2895.84
UNION SELECT '2009-01-20 11:00:00.000', 3074.24
UNION SELECT '2009-01-20 12:00:00.000', 4502.02
UNION SELECT '2009-01-20 13:00:00.000', 3925.09

declare	@ref_date	datetime,
	@hour_slot		int

select	@hour_slot		= 3
select	@ref_date	= min([date])
from	@foo


select	datediff(hour, @ref_date, [date]) / @hour_slot, 
        min([date]), 
        max([date]), 
        sum([value])
from	@foo
group by datediff(hour, @ref_date, [date]) / @hour_slot

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-24 : 03:11:29

Got it working now.Thanks for all the help khtan.


DECLARE @foo TABLE (
	[date] DATETIME
	, [value] MONEY
	)

INSERT @foo ([date], [value])
      SELECT '2009-01-20 07:00:00.000', 0
UNION SELECT '2009-01-20 08:00:00.000', 3104.04
UNION SELECT '2009-01-20 09:00:00.000', 3022.31
UNION SELECT '2009-01-20 10:00:00.000', 2895.84
UNION SELECT '2009-01-20 11:00:00.000', 3074.24
UNION SELECT '2009-01-20 12:00:00.000', 4502.02
UNION SELECT '2009-01-20 13:00:00.000', 3925.09

declare	@ref_date	datetime,
	@hour_slot		int

select	@hour_slot		= 4
select	@ref_date	= min([date])
from	@foo


select	datediff(hour, @ref_date, [date]) / @hour_slot, 
        min([date]), 
        max(DATEADD(hh,1,[date])), 
        sum([value])
from	@foo
group by datediff(hour, @ref_date, [date]) / @hour_slot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources