Grouping By Time Range - 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 2000 Forums

SQL Server Development (2000)

Grouping By Time Range

Author

Topic

NickyJ
Starting Member

46 Posts

Posted - 2008-04-08 : 17:00:14

I have data as below and require 2 recordsets against the data.

Name Time Code
A Smith 08:12 CEE
A Smith 08:14 CEE
A Smith 08:46 CEE
A Smith 08:48 FFR
A Smith 11:12 FFR

Now first I need a total count for all activities for every 5 minutes starting from on the hour which I have got by the following :

SELECT Time,
COUNT(*) as [Count]
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',time)/5.0)*5
,'20000101'),108)
FROM tablename) AS Time(Time)
GROUP BY Time

This gives me my totals every 5 mins as below :

08:05 0 so between 08:00 and 08:04:59
08:10 0
08:15 2
etc.....

Now I need also to count and group by name and code every 5 minutes but can't get this to work,any help greatly appreciated.

Need data to look like this :

Name Time Code Count
A Smith 08:05 NULL 0
A Smith 08:10 NULL 0
A Smith 08:15 CEE 2
A Smith 08:20 NULL 0
A Smith 08:25 NULL 0
A Smith 08:30 NULL 0
A Smith 08:35 NULL 0
A Smith 08:40 NULL 0
A Smith 08:45 NULL 0
A Smith 08:50 CEE 1
A Smith 08:50 FFR 1

etc....

Many thanks in advance

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 17:18:33

SELECT name, code, Time,
COUNT(*) as [Count]
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',time)/5.0)*5
,'20000101'),108)
FROM tablename) AS Time(Time)
GROUP BY name, code, Time

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

NickyJ
Starting Member

46 Posts

Posted - 2008-04-09 : 03:46:14

Thanks for response but get msg..

Server: Msg 207, Level 16, State 1, Line 9
Invalid column name 'name'.
Server: Msg 207, Level 16, State 1, Line 9
Invalid column name 'code'.

as those columns are not within derived table, when I did add them then I get

Server: Msg 8158, Level 16, State 1, Line 1
'Time' has more columns than were specified in the column list.

NickyJ
Starting Member

46 Posts

Posted - 2008-04-09 : 04:14:41

Have sorted this now as follows :

SELECT Time.Time,
Time.Name,
Time.Code,
COUNT(*) as [Count]
FROM
(SELECT CONVERT(CHAR(5),
DATEADD(MINUTE,
FLOOR(DATEDIFF(MINUTE,'20000101',time)/5.0)*5
,'20000101'),108) as Time, Name, Code
FROM vw_PowerHour) AS Time
GROUP BY Time, Name, Code

Thanks for help...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources