Group by 4 hours - 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)

Group by 4 hours

Author

Topic

ranalk
Starting Member

49 Posts

Posted - 2010-06-16 : 04:13:02

Hi Guys,

I need the count result to be grouped by 4 hours.
The column type is Datetime and I am running on few days back data.
e.g. (no meaning from when to when):
Count | Date
10 12:00-16:00
5 16:00-20:00

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-16 : 05:12:57

[code]
select datepart(hour, DateCol) / 4, count(*)
from yourtable
group by datepart(hour, DateCol) / 4
[/code]

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

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2010-06-16 : 05:19:54

select slot, count(*) from
(
select
case
when convert(varchar(5),mydatefield,108) >= '00.00' and convert(varchar(5),mydatefield,108) <= '03.59' then 'slot1'
when convert(varchar(5),mydatefield,108) >= '04.00' and convert(varchar(5),mydatefield,108) <= '07.59' then 'slot2'
when convert(varchar(5),mydatefield,108) >= '08.00' and convert(varchar(5),mydatefield,108) <= '11.59' then 'slot3'
when convert(varchar(5),mydatefield,108) >= '12.00' and convert(varchar(5),mydatefield,108) <= '15.59' then 'slot4'
when convert(varchar(5),mydatefield,108) >= '16.00' and convert(varchar(5),mydatefield,108) <= '19.59' then 'slot5'
when convert(varchar(5),mydatefield,108) >= '20.00' and convert(varchar(5),mydatefield,108) <= '23.59' then 'slot6'
end as "slot"
from mytable
) a
group by slot

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-06-16 : 05:32:34

Here is the Query
Column Time1 DataType is DATETIME
This query will return only for Time in Standard Time Local Time, For UTC please add the other hrs in the case statement
Select
Count(*),
-- Convert(VARCHAR(10), Time1 ,108) , DATEPART(hh,Time1) ,
CASE WHEN DATEPART(hh,Time1) / 4 = 0 THEN
1
WHEN DATEPART(hh,Time1) / 4 = 1 THEN
2
WHEN DATEPART(hh,Time1) / 4 = 2 THEN
3
ELSE
2
END
from dbo.TableA

GROUP BY
CASE WHEN DATEPART(hh,Time1) / 4 = 0 THEN
1
WHEN DATEPART(hh,Time1) / 4 = 1 THEN
2
WHEN DATEPART(hh,Time1) / 4 = 2 THEN
3
ELSE
2
END

Lets unLearn

ranalk
Starting Member

49 Posts

Posted - 2010-06-16 : 08:46:46

Hi,

Thank you all, but the problem is that you all grouped by hour, and I am running on few days. Therefore it aggregated all days...i need to distinguish between days as well.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-16 : 08:48:01

[code]SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, theDateTimeColumnNameHere) / 4 * 4, 0),
COUNT(*)
FROM Table1
GROUP BY DATEDIFF(HOUR, 0, theDateTimeColumnNameHere) / 4 * 4[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources