Combining four while looping into one

Question

Post reply

Combining four while looping into one

koti.raavi

SSCrazy

Points: 2756
More actions
December 12, 2015 at 8:44 am

#313905

I want to combine four while loops into,any help greatly appreciated
drop table #cad
/*GETTING THE REQUIRED MONTHS FOR PERFORMING THE FURTHER CALCULATIONS*/
SELECT [FINALEID],ResourceHome,[ROLLOFFDATE] ,PROJECT,CLIENT,ENGAGEMENT,WBSE
INTO #CAD
FROM CRD
ORDER BY [FINALEID] DESC
Declare @I int
Declare @Sql Nvarchar(MAX),@Sql1 Nvarchar(max)
Set @I=24
WHILE (@I>=0)
BEGIN
Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1+@I,0))
Set @Month='LASTDAYOFFMONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' DateTime'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
Set @I=@I-1
END
Set @I=0
WHILE (@I<=24)
BEGIN
---Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DATEADD(mm, DATEDIFF(mm, 0, GETDATE())++@I, 0)
Set @Month='FIRSTDAYOFMONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' DateTime'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
set @I=@I+1
END
Set @I=0
WHILE (@I<=24)
BEGIN
---Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DatePart(M,DateAdd(M,@I,Getdate()))
Set @Month='MONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' int'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
set @I=@I+1
END
Set @I=0
WHILE (@I<=24)
BEGIN
-----Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DatePart(YYYY,DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+@I, 0))
Set @Month='YEARFORMONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' int'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
set @I=@I+1
END
Thanks,
Dan

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23731 More actions · Answer 1

Please post consumable sample test data from the CAD table... So that we have something to test with.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

koti.raavi (12/12/2015)
I want to combine four while loops into,any help greatly appreciated
drop table #cad
/*GETTING THE REQUIRED MONTHS FOR PERFORMING THE FURTHER CALCULATIONS*/
SELECT [FINALEID],ResourceHome,[ROLLOFFDATE] ,PROJECT,CLIENT,ENGAGEMENT,WBSE
INTO #CAD
FROM CRD
ORDER BY [FINALEID] DESC
Declare @I int
Declare @Sql Nvarchar(MAX),@Sql1 Nvarchar(max)
Set @I=24
WHILE (@I>=0)
BEGIN
Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1+@I,0))
Set @Month='LASTDAYOFFMONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' DateTime'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
Set @I=@I-1
END
Set @I=0
WHILE (@I<=24)
BEGIN
---Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DATEADD(mm, DATEDIFF(mm, 0, GETDATE())++@I, 0)
Set @Month='FIRSTDAYOFMONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' DateTime'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
set @I=@I+1
END
Set @I=0
WHILE (@I<=24)
BEGIN
---Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DatePart(M,DateAdd(M,@I,Getdate()))
Set @Month='MONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' int'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
set @I=@I+1
END
Set @I=0
WHILE (@I<=24)
BEGIN
-----Declare @Month Varchar(50),@Date Varchar(400)
Set @Date=DatePart(YYYY,DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+@I, 0))
Set @Month='YEARFORMONTH'+CONVERT(VARCHAR,@I)
SET @Sql ='ALTER TABLE #CAD ADD ' + @Month + ' int'
Exec (@Sql)
Set @Sql1='Update #CAD Set '+ @Month+ '= '''+@Date+''''---where FYYEAR=@FYYEAR
Exec (@Sql1)
set @I=@I+1
END
Thanks,
Dan

Don't bother, Dan... the While Loops aren't the problem here.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions