Divide query result in 15 minutes 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)

Divide query result in 15 minutes periods

Author

Topic

jh_sql
Starting Member

24 Posts

Posted - 2010-09-21 : 08:32:08

Hello

I have query wich is searched between 2 datetime fields. Purpose is that i could get product usage in 15 minutes intervals.

Datetimes can be overlapping. For example:

Product1 start time: 12:00, end time: 14:00
Product1 start time: 13:00, end time: 15:00

So i should get
12:00 - 13:00 resulting as 1 in use
13:00 - 14:00 resulting as 2 in use
14:00 - 15:00 resulting as 1 in use

Ofcourse those in 15 minutes interval aswell :)

Can anyone give me some tips, how this would be possible? I could do this in code by searching each 15 minutes by day with separate queries, but its horribly inefficient..

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-21 : 08:36:37

where do you present the results.

I'd go for something like:

1) Return a nice list of start and end times ordered by start time. Store this in whatever container you want in the application layer.

2) Iterate over that list in your application for 15 minute slices and build up your report

=============================

You *can* of course write some sort of complicated SQL query but I think this would be better done in the app.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2010-09-21 : 10:17:56

DECLARE @T TABLE (name varchar(10),start datetime,endtime datetime)

INSERT @t VALUES('Product 1',CAST('Sep 21 2010 12:00PM' as datetime),CAST('Sep 21 2010 02:00PM' as datetime))
INSERT @t VALUES('Product 2',CAST('Sep 21 2010 01:00PM' as datetime),CAST('Sep 21 2010 03:00PM' as datetime))

declare @st as datetime
declare @end as datetime

select @st = MIN(start),@end = MAX(endtime) from @t
print @st
print @end

;with timeslots as
(

select DATEADD(mi,15*(count1-1),@st) as ts_f,DATEADD(mi,15*count1,@st) as ts_t from
(
select row_number() over (order by id desc) [count1] from sysobjects
) as a where count1<(DATEDIFF(hh, @st,@end)*60/15) + 1
)

select p.name,ts.* from @T as p,timeslots as ts
where ts_f between p.start and p.endtime
and ts_t between p.start and p.endtime

jh_sql
Starting Member

24 Posts

Posted - 2010-09-22 : 01:09:53

Thanks for replies, its for .net web store statistics.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-22 : 04:45:24

I blogged about this exact thing not too long ago! ->

http://thefirstsql.com/2010/06/23/finding-the-most-active-15-minute-time-period/

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-22 : 04:55:34

Hay - Lumbago. No one reads your blog?

(I've bookmarked it)

Still easier (and probably more performant) though to just return a simple list and do the processing in the front end. This is a task that SQL is not particularly suited for, and the db server is probably better left to be servicing the usual workload.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-22 : 05:08:50

Nah, not very many readers but I haven't been very active either. Building an audience takes time I guess

And to be honest I don't have very high expectations for it either, I'm just posting when I feel like it and hoping that some people might find it useful.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

jh_sql
Starting Member

24 Posts

Posted - 2010-09-22 : 06:30:32

Looking out purely on complexicity of how i should get result i had to start doing this by your suggestion Transact-Charlie :)

Interesting looking blog there Lumbago.

Is that With command in SQL new in sqlserver 2005? Got errors from it in sqlserver 2000 (my developement server is on 2000, production runs in 2005)

I have really overlooked so far whole temporary table options in sql, but i guess i really have to learn about it at some point.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-09-22 : 06:37:28

Yes, the WITH (which refers to what is called a Common Table Expression, CTE) is available in 2005 as well as "derived tables". The two (more or less related concepts) are ultimately powerful and will open up a world of possibilities for you once you get the hang of it

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources