April 21, 2015 at 9:50 am
Hi all,
I want to insert data(month&year)from 2014 till now - into temp table using 2 while loop.
drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint
)
Declare @year int=2014
Declare @Month int = 1
Declare @Currentyear int =(select year(getdate()))
Declare @Currentmonth int =(select month(getdate()))
While (@year <= @Currentyear)
Begin
While ((@Month <= @Currentmonth and @year= @Currentyear )) or (@Month <= 12 and @year< @Currentyear )
Begin
insert into #loop([month] ,[year]
values(@Month,@year)
set @Month = @Month +1
End
set @year = @year +1
End
select * from #loop
For some reason I cant not get 2015 data .
Anyone have an idea why ?
Thanks
April 21, 2015 at 11:01 am
This is a job for a Tally Table! Read this article and it will change your life:
The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url]
Using a tally table you can accomplish this like so:
DECLARE @year int=2014;
WITH
YR(D) AS (SELECT CAST('1/1/'+CAST(@year AS char(4)) AS date)),
L1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) t(N)),
iTally(N) AS
(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM L1 a, L1 b, L1 c
)
SELECT
SequenceNbr = N+1,
MonthNbr = MONTH(DATEADD(MONTH,N,D)),
YearNbr = YEAR(DATEADD(MONTH,N,D))
FROM iTally
CROSS JOIN YR
WHERE N <= DATEDIFF(MONTH,D,GETDATE());
-- Itzik Ben-Gan 2001
April 21, 2015 at 12:17 pm
the problem I was facing was that month is not reset after the second loop run.
so month should be reset in the first loop before second loop starts.
While (@year <= @Currentyear)
set @month = 1
Begin
While ((@Month <= @Currentmonth and @year= @Currentyear )) or (@Month <= 12 and @year< @Currentyear )
Thank you for your help tho
April 21, 2015 at 12:32 pm
You don't need a while loop or a nested while loop to accomplish the task you are attempting to complete. Alan.B has shown you a much better way to accomplish your task.
April 21, 2015 at 12:35 pm
caojunhe24 (4/21/2015)
the problem I was facing was that month is not reset after the second loop run.so month should be reset in the first loop before second loop starts.
While (@year <= @Currentyear)
set @month = 1
Begin
While ((@Month <= @Currentmonth and @year= @Currentyear )) or (@Month <= 12 and @year< @Currentyear )
Thank you for your help tho
Glad you solved your problem. I would, however, seriously consider reading the article I posted and begin developing SQL that does not use loops. You can make your queries thousands of times faster by switching to a set-based approach to problems like this (something I learned from people like Lynn) 😎
-- Itzik Ben-Gan 2001
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply