SQL Query based on a while loop

Question

I am trying to construct an sql query using a while loop that increments a datetime by one minute each iteration and then generates a select statement based on the time:

declare @dt datetime
set @dt = '2011-7-21'
while @dt < '2011-7-22'
begin
    select Count(*) From Actions Where Timestamp = @dt
    set @dt = DATEADD(mi, 1, @dt)
end

The query works as intended except that every iteration of the while loop seems to produce a new query entirely, rather than simply a new row. Is there a way to construct this so that its one single query and each row is generated by the incrementation of the loop? I believe this occurs because the select statement is inside the loop, but I'm not sure how to construct it a different way that works.

EDIT - Here is what I came up with using a temporary table, but it is slow. Maybe there is a faster way? If not thats fine, atleast this works:

create table #temp 
(
  [DT] datetime not null,
  [Total] int not null
)

declare @dt datetime
declare @result int
set @dt = '2011-7-21'
while @dt < '2011-7-22'
begin
    set @result = Count(*) From Actions Where Timestamp = @dt
    insert #temp ([DT],[Total]) values (@dt, @result)
    set @dt = DATEADD(mi, 1, @dt)
end

select * from #temp;

drop table #temp;

Will all of the datetime values in your table have a seconds chunk of 00? How about milliseconds? Or are you looking only for those rows that were created precisely on the tick of the minute? set @dt = '2011-7-21' ... Where Timestamp = @dt — Tim
– Tim, Commented Jul 22, 2011 at 19:56

SQLMenace · Accepted Answer · 2011-07-22 19:02:06Z

2

One way by using a table of numbers

declare @dt datetime
set @dt = '2011-07-21'

select DATEADD(mi, number, @dt) 
from master..spt_values
where type = 'P'
and DATEADD(mi, number, @dt)  < '2011-07-22'

If you have your own number table, use that

See here for more info http://wiki.lessthandot.com/index.php/Date_Ranges_Without_Loops

you full query would be like

DECLARE @dt DATETIME
SET @dt = '2011-07-21'

SELECT x.SomeTime,y.TheCount FROM 
(SELECT DATEADD(mi, number, @dt) as SomeTime FROM master..spt_values
WHERE TYPE = 'P'
AND DATEADD(mi, number, @dt)  < '2011-07-22') x
LEFT JOIN (
    SELECT TIMESTAMP, COUNT(*) AS TheCount
    FROM Actions
    GROUP BY TIMESTAMP
) AS y
ON x.SomeTime = dateadd(mi, datediff(mi, 0, y.Timestamp)+0, 0)

edited Jul 22, 2011 at 19:02

answered Jul 22, 2011 at 18:38

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Lamak Over a year ago

This doesn't take in consideration the COUNT that @Sean Thoman needs to do

Sean Thoman Over a year ago

Thanks, mentioning using a table got me thinking. See my edit -- got it working using a temporary table. Only problem is performance but its not a huge issue.

SQLMenace Over a year ago

added, also did the minute rounding thingy

Cade Roux · Accepted Answer · 2011-07-22 18:42:00Z

2

If you have a numbers table (from 0 to a million or whatever), this is relatively simple:

SELECT *
FROM Numbers AS n
LEFT JOIN (
    SELECT Timestamp, COUNT(*) AS Ct
    FROM Actions
    GROUP BY Timestamp
) AS ActionSummary
    ON ActionSummary.Timestamp = DATEADD(mi, n.Number, '2011-07-21')
WHERE DATEADD(mi, n.Number, '2011-07-21') < '2011-07-22'
ORDER BY DATEADD(mi, n.Number, '2011-07-21')

No need for loops.

There's ways to optimize this, but that should be fairly understandable as it is.

Also note that the timestamps cannot have any seconds or fractions of a second for this to work (your original has this problem as well).

answered Jul 22, 2011 at 18:42

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Collectives™ on Stack Overflow

SQL Query based on a while loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related