Can this be done without a loop??? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Can this be done without a loop???

Author

Topic

JBelthoff
Posting Yak Master

173 Posts

Posted - 2009-04-10 : 11:52:51

Hi All,

What is the best way to do this without a loop. I'm looking for Number of Clicks seperated by 15 minute blocks of time going back X number days.

Here is my loop..... Thanks!


Set @SDate = '2009-04-06 00:00:00.000'
Set @EDate = '2009-04-06 00:14:59.999'
Set @StopDate = '2009-04-10 00:00:00.000'

Set NoCount On

while @SDate < @StopDate

Begin

	select count(*) as [Clicks], Convert(varchar(20), @SDate) As [Time Period Starting]
	from dbo.Tracking
	where action = 'Click'
	and CreateDate >= @SDate
	and CreateDate < @EDate

Set @SDate = DateAdd( n, 15, @SDate)
Set @EDate = DateAdd( n, 15, @EDate)

End

Set NoCount Off

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
› As far as myself... I do this for fun!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-04-10 : 12:59:45

[code]declare @start datetime, @stop datetime

select @start='2009-04-06 00:00:00.000', @stop ='2009-04-10 00:00:00.000'

;with numbers (n) AS (select 0 n union all select n+1 from numbers where n+1<1001)
select count(*) as [Clicks], Convert(varchar(20), dateadd(minute, N.n*15, @start), 120) As [Time Period Starting]
from dbo.Tracking T
inner join numbers N on T.CreateDate between dateadd(minute, N.n*15, @start) and dateadd(minute, (N.n+1)*15, @start)
where T.CreateDate between @start and @stop
group by dateadd(minute, N.n*15, @start)
order by [Time Period Starting]
option (maxrecursion 1000)[/code]edit: fixed a bad table and column reference, sorry about that

JBelthoff
Posting Yak Master

173 Posts

Posted - 2009-04-10 : 13:12:06

Brilliant!!!!!!

Thank you!

JBelthoff
• Hosts Station is a Professional Asp Hosting Provider
› As far as myself... I do this for fun!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources