While loop fro SQL 2005 not working 1

[vba317]

I have tried to write a WHILE loop for SQL 2005 unsucessfully.
What I am trying to do is have a negative value added to rptpddiff. If the update was done properly I would have
rptpd 395 = 0 , rptpd 396 = -1, rptpd 397 =-2, rptpd 398 = -3 and so on. Currently I am updating the correct records but they are all the same value instead of being incremented by -1. I am hoping to get some help.

Tom


DATA

uci clntid rptpd rptpddiff fy fyord fydiff imp
EMA 15 394 1 2015 1 0 0
EMA 15 395 0 2015 2 0 0
EMA 15 396 0 2015 3 0 0
EMA 15 397 0 2015 4 0 0
EMA 15 398 0 2015 5 0 0
EMA 15 399 0 2015 6 0 0
EMA 15 400 0 2015 7 0 0
EMA 15 401 0 2015 8 0 0
EMA 15 402 0 2015 9 0 0
EMA 15 403 0 2015 10 0 0
EMA 15 404 0 2015 11 0 0
EMA 15 405 0 2015 12 0 0

DECLARE @rptpddiff int, @uci varchar(50)

SET @rptpddiff = 1
WHILE (@rptpddiff <=-12)
BEGIN
SET @uci = 'EMA'

UPDATE rptdata_monthly.dbo.rpt_FYInfo
SET  rptpddiff= @rptpddiff
FROM rptdata_monthly.dbo.rpt_FYInfo
WHERE uci=@uci AND rptpddiff =0 AND fydiff = 0 AND imp =0 AND rptpd >395
SET @rptpddiff = @rptpddiff -1
END 
GO

 

[gmmastros]

You set your control variable to 1 and then loop while the control variable is less than or equal to negative 12. Try changing this:

WHILE (@rptpddiff <=-12)

To this:

WHILE (@rptpddiff [!]>=[/!]-12)

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vba317]

Thanks George that was it