T SQL While Loop - Into a Temp table for final output
-
Hi All,
I have a lengthy SQL that is performing a while loop to iterate through each day of the year and save off several columns of data (1-4 rows per day).
I do not use While Loops very often, so it is not my strong suit. The issue I’m having is getting the final results to save off into a single table for output.
I’m not going to list the entire SQL, but will try and provide the key areas.
DECLARE @StartDateTime DATETIME = '2019-09-01T00:00:00' ;
DECLARE @EndDateTime DATETIME = '2019-09-01T23:59:59';
DECLARE @Location varchar(50);
DECLARE @StartTimeUTC datetime; --*
DECLARE @EndTimeUTC datetime; --*
DECLARE @DSTFlag BIT; --*
SET @Location = 'Orlando';
DECLARE @ENDLOOPDATE DateTIME;
SET @ENDLOOPDATE = '2019-09-05 23:59:59'
WHILE (@StartDateTime <= @ENDLOOPDATE)
BEGIN
Print @StartDateTime;
-- ****** omitted lines of the primary SQL *****
DECLARE @Output table
(
RowID int not null identity(1,1) primary key,
DateOfYear DateTime,
DNIS VARCHAR(20),
DNISReportDescription VARCHAR(25),
DNISReportCategory VARCHAR(20),
Over15s INT NULL,
AgentQueueAnswered INT NULL
)
declare @i int
select @i = min(RowID) from @Output
declare @max-2 int
select @max-2 = max(RowID) from @Output
while @i <= @max-2 begin
select DateOfYear, Dnis, DNISReportDescription, DNISReportCategory, Over15s, AgentQueueAnswered
from @Output where RowID = @i
set @i = @i + 1
end
SET @StartDateTime = DATEADD(day,1, @StartDateTime)
SET @EndDateTime = DATEADD(day,1, @EndDateTime)
END;
Note: I will be comparing this approach with an alternative method by which I return the date needed in the underlying data so I can avoid a while loop….but first I want to get this to work so I gain experience on using while loops….I suffer from not wanting to give up just because I can’t get it to work ??.
Appreciate any suggestions !!!
Cervelo
-
Suggestion #1, please format your code in future. See how much easier it is to read ...
DECLARE @StartDateTime DATETIME = '2019-09-01T00:00:00';
DECLARE @EndDateTime DATETIME = '2019-09-01T23:59:59';
DECLARE @Location VARCHAR(50);
DECLARE @StartTimeUTC DATETIME; --*
DECLARE @EndTimeUTC DATETIME; --*
DECLARE @DSTFlag BIT; --*
SET @Location = 'Orlando';
DECLARE @ENDLOOPDATE DATETIME;
SET @ENDLOOPDATE = '2019-09-05 23:59:59';
WHILE (@StartDateTime <= @ENDLOOPDATE)
BEGIN
PRINT @StartDateTime;
-- ****** omitted lines of the primary SQL *****
DECLARE @Output TABLE
(
RowID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
,DateOfYear DATETIME
,DNIS VARCHAR(20)
,DNISReportDescription VARCHAR(25)
,DNISReportCategory VARCHAR(20)
,Over15s INT NULL
,AgentQueueAnswered INT NULL
);
DECLARE @i INT;
SELECT @i = MIN(RowID)
FROM @Output;
DECLARE @max INT;
SELECT @max = MAX(RowID)
FROM @Output;
WHILE @i <= @max
BEGIN
SELECT DateOfYear
,DNIS
,DNISReportDescription
,DNISReportCategory
,Over15s
,AgentQueueAnswered
FROM @Output
WHERE RowID = @i;
SET @i = @i + 1;
END;
SET @StartDateTime = DATEADD(DAY, 1, @StartDateTime);
SET @EndDateTime = DATEADD(DAY, 1, @EndDateTime);
END;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
You should declare your table variable outside of the WHILE loop, otherwise it will keep getting reinitialized.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
You should declare your table variable outside of the WHILE loop, otherwise it will keep getting reinitialized.
Phil, I agree that the table should be declared outside of the loop, but only for aesthetic reasons. DECLAREs are evaluated at parse time, not run time, so the table doesn't get reinitialised.
DECLARE @i int = 0;
WHILE @i < 10
BEGIN
DECLARE @t table (j int);
INSERT INTO @t (j) VALUES (@i);
SELECT j FROM @t;
SET @i = @i + 1;
ENDJohn
-
You never populate your
@Outputtable.Also, single-row
WHILEloops tend to perform worse thanCURSORs. I see nothing in your code that would require to you process a single row at a time. You may be able to load the entire table at once. If you do need to load in batches, you should look into usingOFFSET/FETCH NEXTwhich was specifically introduced to handle paging.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA - John Mitchell-245523 wrote:Phil Parkin wrote:
You should declare your table variable outside of the WHILE loop, otherwise it will keep getting reinitialized.
Phil, I agree that the table should be declared outside of the loop, but only for aesthetic reasons. DECLAREs are evaluated at parse time, not run time, so the table doesn't get reinitialised.
DECLARE @i int = 0;
WHILE @i < 10
BEGIN
DECLARE @t table (j int);
INSERT INTO @t (j) VALUES (@i);
SELECT j FROM @t;
SET @i = @i + 1;
ENDJohn
Wow! You learn something every day. I was so sure of myself, I didn't even test it (clearly!). Thanks, John.
I need to go and lie down to recover.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thank you everyone for your feedback!!
My apologies but I'm still a little confused. I actually have two while loops happening here.... mostly because the first while loop output each data set separately and I googled for a solution that would give me a single data set output.
My take away was that I thought I might need the 2 while loop. I'm going to remove the 2nd loop and start my question over.
If I have 1 while loop like such
DECLARE @StartDateTime DATETIME = '2019-09-01T00:00:00';
DECLARE @EndDateTime DATETIME = '2019-09-01T23:59:59';
DECLARE @ENDLOOPDATE DATETIME;
SET @ENDLOOPDATE = '2019-09-05 23:59:59';
WHILE (@StartDateTime <= @ENDLOOPDATE)
BEGIN
PRINT @StartDateTime;
--- Some SQL
Final script is a Select * From
(
UNION ALL
)
SET @StartDateTime = DATEADD(day,1, @StartDateTime)
SET @EndDateTime = DATEADD(day,1, @EndDateTime)
END;
This is what I'm getting
Can you tell me what I'm missing here?
-
Why do you refuse to format your code?
Your SELECT should be outside of your WHILE loop.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Thank you Phil for the constructive criticism... I think I will go lie down now.
-
I would use a tally table here instead of either loop. Whenever you start using loops to query data you are very likely not doing it the most efficiently. Read Jeff Moden's spectacular article about what a tally table is and how it can replace a loop. http://www.sqlservercentral.com/articles/T-SQL/62867/
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Thank you Sean!!
I will review the article you sent. I did get the while loop to work, but as you said it was not very efficient when pulling longer date ranges, so I started modifying one of the underlying SP and some code to work around the issue. Still in progress, so not to late to try the Tally table approach.
Again thank you for sharing your knowledge with me!!
Cervello
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply