Query group by 15 mins - 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

Transact-SQL (2000)

Query group by 15 mins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-22 : 07:46:38

Gary writes "Is it possible to create a query that will summarize based on a data range and for every x minutes?

For example, I have a table like:
Date Event
1/1/05 01:00 Eat
1/1/05 01:15 Eat
1/1/05 01:00 Sleep

I want to get the number of eats and sleeps for the day in 15 minute intervals.

Select count(*), Event
where date between ??? and ???
group by Event

Any ideas?

Thank you,

GB"

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 09:08:50

Select count(*), Event, DATEADD(Minute, (DATEDIFF(Minute, 0, MyDate)/15)*15, 0)
FROM MyTable
group by Event, DATEADD(Minute, (DATEDIFF(Minute, 0, MyDate)/15)*15, 0)

Kristen

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-22 : 09:17:57

... and my $2:

select

convert(varchar(10), dt, 110),

datepart(mi, dt)/15,

sum(case when evnt='Eat' then 1 else 0 end) as 'EatCount',

sum(case when evnt='Eat' then 0 else 1 end) as 'SleepCount'

from myTable where dt between ??? and ???

group by convert(varchar(10), dt, 110), datepart(mi, dt)/15

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 09:33:25

Trouble sorting [chronologically] by column 1 once it is in varchar format?

Kristen

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-22 : 12:49:58

Sorting is up to the poster (AskSQLTeam) :|
Btw, I think I have to be corrected:

select

convert(varchar(10),dt,110),

(datepart(hh,dt)*60 + datepart(mi,dt))/15,

sum(case when evnt='Eat' then 1 else 0 end) as 'EatCount',

sum(case when evnt='Eat' then 0 else 1 end) as 'SleepCount'

from myTable group by

convert(varchar(10),dt,110), (datepart(hh,dt)*60 + datepart(mi,dt))/15

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-22 : 14:25:48

The approach that Kristen suggested seems simpler. It is probably faster, since it leaves the time as a datetime, instead of converting it to a character string, and there are fewer columns to group and sort on.

It has a lot of flexibility also. For example, group on the quarter hour, starting at 2 minutes after the hour.


Select
	[Count] = count(*),
	Event,
	Quarter_Hour =
		dateadd(mi,(datediff(mi,'00:02',MyDate)/15)*15,'00:02')
from
	MyTable
group by
	Event,
	dateadd(mi,(datediff(mi,'00:02',MyDate)/15)*15,'00:02')

CODO ERGO SUM

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-22 : 15:52:02

quote:
Originally posted by Michael Valentine Jones

The approach that Kristen suggested seems simpler. It is probably faster, since it leaves the time as a datetime, instead of converting it to a character string, and there are fewer columns to group and sort on.

It has a lot of flexibility also. For example ...

I agree! :|

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 16:11:07

Can you guys advise on how I get my enlarged head through the door so I can go home, please?

"Sorting is up to the poster (AskSQLTeam)"

Stoad (FWIW) the problem I perceive with this is that you would need to wrap the query (making it a sub-query) in order to do the sort (because otherwise you've got to mess around with the aggregates). Now, having done this for years, it doesn't presents much of a threat to me. But my troops look at it and say "WTF ..." and that basically means that in 6 months the poor chap that is asked to maintain it doesn't undertstand it either!

My

DATEADD(... DATEDIFF(... ) )

is obtuse too, but my troops know about it - its recognised, here, as being the most efficient way to do date modulo. So its not seen to be threatening ...

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-03-22 : 16:32:44

Well if people are threatened by it, you could always suggest that they add a COMMENT to their code.

group by
	Event,
	-- Convert MyDate to 15 minute intervals for grouping
	-- Talk to Kristen for an explanation 
	-- of how this SQL magic works. Or RTF BOL
	dateadd(mi,(datediff(mi,0,MyDate)/15)*15,0)

quote:
Originally posted by Kristen
My
DATEADD(... DATEDIFF(... ) )
is obtuse too, but my troops know about it - its recognised, here, as being the most efficient way to do date modulo. So its not seen to be threatening ...

CODO ERGO SUM

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 16:39:34

Kewl!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources