Collecting time information in periods

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)

Collecting time information in periods

Author

Topic

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-27 : 11:35:10

Hello,

i have a table with some time information of electric measurements (when they were made):

table A
ID|TIME
1|00:02
2|00:29
3|00:47
4|01:21
...

Now i want to create a procedure that gives an output like that:
Time|Number_of_measurements
00:00-01:00|3
01:00-02:00|12
....

('00:00-01:00' can also be an integer that stands for that period)

I would appreciate if you can give me some ideas how for the fastest way to produce the desired output.

Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-27 : 11:41:03

[code]SELECT DATEADD(hh,DATEDIFF(hh,0,Date),0),COUNT(ID)
FROM
(
SELECT DATEADD(hh,0,TIME),ID AS Date
FROM Table
)t
GROUP BY DATEADD(hh,DATEDIFF(hh,0,Date),0)[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-27 : 12:15:10

quote:

Originally posted by visakh16

SELECT DATEADD(hh,DATEDIFF(hh,0,Date),0),COUNT(ID)
FROM
(
SELECT DATEADD(hh,0,TIME),ID AS Date
FROM Table
)t
GROUP BY DATEADD(hh,DATEDIFF(hh,0,Date),0)

Shouldn't it be

SELECT		DATEADD(HOUR, DATEDIFF(HOUR, '19000101', Time), '19000101'),
		COUNT(*)
FROM		Table
GROUP BY	DATEADD(HOUR, DATEDIFF(HOUR, '19000101', Time), '19000101')
ORDER BY	DATEADD(HOUR, DATEDIFF(HOUR, '19000101', Time), '19000101')

SELECT		CONVERT(CHAR(5), thePeriod, 108)
		+ '-' +	CASE DATEPART(HOUR, thePeriod)
				WHEN 23 THEN '24:00'
				ELSE CONVERT(CHAR(5), DATEADD(HOUR, 1, thePeriod), 108)
			END
FROM		(
			SELECT		DATEADD(HOUR, DATEDIFF(HOUR, '19000101', Time), '19000101') AS thePeriod,
					COUNT(*)
			FROM		Table
			GROUP BY	DATEADD(HOUR, DATEDIFF(HOUR, '19000101', Time), '19000101')
			ORDER BY	DATEADD(HOUR, DATEDIFF(HOUR, '19000101', Time), '19000101')

		) AS d
ORDER BY	thePeriod

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

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-28 : 11:44:26

Thank you guys, this solution is by far better than my idea was :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 12:08:14

[code]SELECT w.theStart + '-' + w.theEnd,
COUNT(*)
FROM (
SELECT '00:00' AS theStart, '01:00' AS theEnd UNION ALL
SELECT '01:00', '02:00' UNION ALL
SELECT '02:00', '03:00' UNION ALL
SELECT '03:00', '04:00' UNION ALL
SELECT '04:00', '05:00' UNION ALL
SELECT '05:00', '06:00' UNION ALL
SELECT '06:00', '07:00' UNION ALL
SELECT '07:00', '08:00' UNION ALL
SELECT '08:00', '09:00' UNION ALL
SELECT '09:00', '10:00' UNION ALL
SELECT '10:00', '11:00' UNION ALL
SELECT '11:00', '12:00' UNION ALL
SELECT '12:00', '13:00' UNION ALL
SELECT '13:00', '14:00' UNION ALL
SELECT '14:00', '15:00' UNION ALL
SELECT '15:00', '16:00' UNION ALL
SELECT '16:00', '17:00' UNION ALL
SELECT '17:00', '18:00' UNION ALL
SELECT '18:00', '19:00' UNION ALL
SELECT '19:00', '20:00' UNION ALL
SELECT '20:00', '21:00' UNION ALL
SELECT '21:00', '22:00' UNION ALL
SELECT '22:00', '23:00' UNION ALL
SELECT '23:00', '24:00'
) AS w
INNER JOIN Table1 AS t1 ON t1.TIME >= w.theStart
AND t1.TIME < w.theEnd
GROUP BY w.theStart + '-' + w.theEnd
ORDER BY w.theStart + '-' + w.theEnd[/code]

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

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-29 : 06:24:22

Thx Peso for your second suggestions, it is similar to the idea i had. But if i work with standard datapart arguments what is the argument to use the second suggestion as it is a) slower and b) harder to handle.

The problem with the first solution is imo that there is not the possiblity to use the datediff with half hours or quarters. If i want to use half hours (05:30, 06:00, 06:30 ....) or quarters (05:15, 05:30, 05:45, ...) is there any better solution than writing all the select statements hard coded as in your second solution?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-29 : 10:24:35

[code]SELECT CONVERT(CHAR(5), thePeriod, 108)
+ '-' + CASE thePeriod
WHEN '23:30' THEN '24:00'
ELSE CONVERT(CHAR(5), DATEADD(MINUTE, 30, thePeriod), 108)
END
FROM (
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', Time) / 30 * 30, '19000101') AS thePeriod,
COUNT(*)
FROM Table
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', Time) / 30 * 30, '19000101')
ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', Time) / 30 * 30, '19000101')

) AS d
ORDER BY thePeriod

SELECT CONVERT(CHAR(5), thePeriod, 108)
+ '-' + CASE thePeriod
WHEN '23:45' THEN '24:00'
ELSE CONVERT(CHAR(5), DATEADD(MINUTE, 15, thePeriod), 108)
END
FROM (
SELECT DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', Time) / 15 * 15, '19000101') AS thePeriod,
COUNT(*)
FROM Table
GROUP BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', Time) / 15 * 15, '19000101')
ORDER BY DATEADD(MINUTE, DATEDIFF(MINUTE, '19000101', Time) / 15 * 15, '19000101')

) AS d
ORDER BY thePeriod[/code]

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

IamHolliday
Starting Member

9 Posts

Posted - 2008-10-29 : 11:35:03

damn i am thinking a way too complicated.

thank you very much peso for your excellect support here.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources