Grouping Dates by Time (hourly) and by Day

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 2008 Forums

Transact-SQL (2008)

Grouping Dates by Time (hourly) and by Day

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2012-10-23 : 11:12:16

Hi, I'm trying to group results from a table by hour range, and then by day. So for example, any records between the hours of 1pm and 2pm on Mondays should be grouped together. Records between 1pm and 2pm on Wednesdays would be counted as a different group. I've read through some other similar posts here and tried to use the same logic but my results are not being grouped.

Results I'm getting:
SvcCampus| Count| TimeIn|
D| 1| 2012-09-18 08:45:00.000|
D| 1| 2012-09-04 09:00:00.000|
D| 1| 2012-09-04 09:15:00.000|
D| 1| 2012-09-04 09:50:00.000|
D| 1| 2012-09-25 09:05:00.000|
D| 1| 2012-09-19 09:40:00.000|
D| 1| 2012-09-06 09:55:00.000|

Results I want:
SvcCampus| Count| TimeIn|
D| 1| 2012-09-18 08:45:00.000|
D| 4| 2012-09-04 09:30:00.000|
D| 1| 2012-09-19 09:40:00.000|
D| 1| 2012-09-06 09:55:00.000|

The red line illustrates how I would like to group the four times that occurred on a Tuesday between the hours of 9:00 and 10:00. I made up the time of 9:30. I don't really care what time the four records become as long as it's something between 9:00 and 10:00.


SELECT SvcCampus, COUNT(*) AS [Count], TimeIn 
FROM (
	SELECT SvcCampus, 
		DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, '20000101', TimeIn)/5.0)*5, '20000101') AS TimeIn
	FROM Visits
	) AS cols
GROUP BY SvcCampus, TimeIn
ORDER BY DATEPART(hh, TimeIn), DATEPART(dw, TimeIn) + @@DATEFIRST ASC

thanks,

CJ

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 11:30:36

[code]SELECT
SvcCampus,
COUNT(*) AS [Count],
MAX(TimeIn) AS [TimeIn]
FROM
Visits
GROUP BY
SvcCampus,
CAST(TimeIn AS DATE),
DATEPART(hour,TimeIn);[/code]

CactusJuice
Starting Member

46 Posts

Posted - 2012-10-23 : 11:47:52

Thank you. Your SQL groups the three records on 9/24 but the record on 9/25 is a separate row. I would like it to be grouped with the three from 9/4 since all four of these fall on a Tuesday between 9 and 10am.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 12:08:19

Ah, I misunderstood what you wanted. See below:

SELECT
	SvcCampus,
	COUNT(*) AS [Count],
	MAX(TimeIn) AS [TimeIn]
FROM
	Visits
GROUP BY
	SvcCampus,
	DATEDIFF(dd,0,TimeIn)%7,
	DATEPART(hour,TimeIn);

CactusJuice
Starting Member

46 Posts

Posted - 2012-10-23 : 12:19:35

Wow! I wish I would have asked here yesterday. I've spent so many hours on this. You made it look so easy. Thank you!

CJ

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-23 : 13:44:03

You are very welcome!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources