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-21 : 10:30:51

[code]
Date Value
2009-01-20 07:00:00.000 0
2009-01-20 08:00:00.000 3104.04
2009-01-20 09:00:00.000 3022.31
2009-01-20 10:00:00.000 2895.84
2009-01-20 11:00:00.000 3074.24
2009-01-20 12:00:00.000 4502.02
2009-01-20 13:00:00.000 3925.09
[/code]

How to get the sum of values in slots of 2 hours?

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-21 : 10:43:13

Not sure what you asking...

Hope can help...but advise to wait pros with confirmation...

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-21 : 10:47:29

I want the sum of values from 2009-01-20 07:00:00.000 to 2009-01-20 09:00:00.000 then again from
2009-01-20 09:00:00.000 to 2009-01-20 11:00:00.000 and so on.
since the difference is 2 hours between them.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-21 : 10:52:34

Does this work?


/*
Date                    Value
2009-01-20 07:00:00.000	0
2009-01-20 08:00:00.000	3104.04
2009-01-20 09:00:00.000	3022.31
2009-01-20 10:00:00.000	2895.84
2009-01-20 11:00:00.000	3074.24
2009-01-20 12:00:00.000	4502.02
2009-01-20 13:00:00.000	3925.09
*/

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

-- Test end and start of day
UNION SELECT '2009-01-20 00:00:01.000', 1231.03
UNION SELECT '2009-01-20 01:59:00.000', 1235.03
UNION SELECT '2009-01-20 22:00:00.000', 122.21
UNION SELECT '2009-01-20 23:59:59.000', 0

SELECT
	samp.[strippedDate]
	, samp.[hourSlot]
	, SUM(samp.[value])
FROM
	(
	SELECT
		CAST(FLOOR(CAST(f.[date] AS FLOAT)) AS DATETIME) AS [strippedDate]
		, f.[value] AS [value]
		, CASE WHEN DATEPART(HOUR, f.[date]) % 2 <> 0 THEN DATEPART(HOUR, f.[date]) - 1 ELSE DATEPART(HOUR, f.[date]) END AS [hourSlot]
	FROM
		@foo f
	)
	samp
GROUP BY
	samp.[strippedDate]
	, samp.[hourSlot]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-22 : 03:36:04

No it fails when the same hour falls on multiple days.The hour slot is dynamic.It can even range from 1 to 100 hours.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 03:55:21

quote:
Originally posted by ayamas

No it fails when the same hour falls on multiple days.The hour slot is dynamic.It can even range from 1 to 100 hours.

what do you mean ?

if the hour slot is 100, what is the reference point ?

can show with more sample and expected result ?

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-22 : 03:58:38

See this sample data
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

Now if reference is 100 hours I need to get the sum of all values from first record till the next 100 hours grouped together.Say if it is 2 hrs the output will be 3104.4 for first slot then
3022.31+ 2895.84 for next slot of 2 hrs & so on.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-22 : 04:03:37

[code]
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, 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-22 : 04:51:52

Thanks khtan it worked.I was little close but could not finish it.I was using this one
select
datepart(hh,date)/hourslot,
sum(value) from @foo
group by datepart(hh,date))/hourslot
order by DATEPART(HH,date).

Thanks once again man.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-07-22 : 04:52:24

quote:
Originally posted by ayamas

No it fails when the same hour falls on multiple days.The hour slot is dynamic.It can even range from 1 to 100 hours.

Don't you think it would have been a good idea to say this in the first place? Then I wouldn't have wasted my time.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-07-22 : 04:56:57

quote:
Originally posted by Transact Charlie

quote:
Originally posted by ayamas

No it fails when the same hour falls on multiple days.The hour slot is dynamic.It can even range from 1 to 100 hours.

Don't you think it would have been a good idea to say this in the first place? Then I wouldn't have wasted my time.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sorry charlie.I am really sorry from my heart.I will be more specific from next time.Thanks for taking troubles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources