CODEWITHSUNDEEP
sqlt-sqlselectwhile-loop
Sean Thoman
Sean Thoman

Reputation: 7499

SQL Query based on a while loop

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;

Upvotes: 3

Views: 3464

Answers (2)

SQLMenace
SQLMenace

Reputation: 135171

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)

Upvotes: 2

Cade Roux
Cade Roux

Reputation: 89741

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).

Upvotes: 2

Related Questions