STEPPING THROUGH A ROW AT A TIME

Question

Post reply

STEPPING THROUGH A ROW AT A TIME

ian.dunlop-843887

Newbie

Points: 5
More actions
October 7, 2009 at 8:22 am

#156295

HI ALL,
I have a a table which contains 3 columns, column 1 is an identity, column 2 contains a start date, column 3 contains an end date. - total rows = 21
I am trying to step through each row - as below
DECLARE @COUNTER INT
SET @COUNTER = 1
DECLARE @MAT_START INT
SET @MAT_START = (SELECT MAT_START FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)
DECLARE @MAT_END INT
SET @MAT_END = (SELECT MAT_END FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)
WHILE @COUNTER < (SELECT MAX(RMAT_ID) FROM ROLLING_MAT)
BEGIN
PRINT @COUNTER
PRINT @MAT_START
PRINT @MAT_END
SET @COUNTER= @COUNTER+1
END
above works except although the counter increases as expected i need it to read the start and end date values in the next row, 1 at at a time - currently I get back...
1
200701
200712
2
200701
200712
3
200701
200712
4
200701
200712
5
200701
200712
6
200701
200712
7
200701
200712
8
200701
200712
9
200701
200712
10
200701
200712
11
200701
200712
12
200701
200712
13
200701
200712
14
200701
200712
15
200701
200712
16
200701
200712
17
200701
200712
18
200701
200712
19
200701
200712
20
200701
200712
These two dates are the first values in row one - i need to to some how get it to read row 1 to get above, then read row 2 and get the vals for that row and so on...
I've tried row_number but keep getting and error - so may not be the best way
Any assistance or solution greatly appreciated
Kind Regards

Viewing 9 posts - 1 through 9 (of 9 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

DECLARE @COUNTER INT

SET @COUNTER = 1

DECLARE @MAT_START INT

DECLARE @MAT_END INT

WHILE @COUNTER < (SELECT MAX(RMAT_ID) FROM ROLLING_MAT)

BEGIN

SET @MAT_START = (SELECT MAT_START FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)

SET @MAT_END = (SELECT MAT_END FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)

PRINT @COUNTER

PRINT @MAT_START

PRINT @MAT_END

SET @COUNTER= @COUNTER+1

END

Should do it..

CEWII

ian.dunlop-843887 Newbie Points: 5 More actions · Answer 2

Thank you - you would not believe the time I have worked on this - sometimes the harder you work the blinder you get - cheers

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

I am curious what it is you are trying to accomplish and why you think you need to use a loop to accomplish it?

ian.dunlop-843887 Newbie Points: 5 More actions · Answer 4

HI, I am looking at grabbing income values from specific time periods MAT (Moving annual total) starting from 200701 up to current - ie. (200701 to 200712 -12 months) 200702 to 200801 -12 months) etc. as the date is not in date format (int) as above I use a table predifined with the various date periods to grab the vals - thats my next step to complete - not working as yet !!

hope this explains

Kind regards

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Not really. If you need help it would be better to ask for it giving the a complete description instead of in small pieces here and there. I'd also recommend reading the first article I have referenced below in my signature block when it comes to knowing what you should post to get the best help, with the added benefit of getting TESTED code in return.

ian.dunlop-843887 Newbie Points: 5 More actions · Answer 6

HI, Thanks again for above- works a treat - last bit honest

I finally need to create a column for each period into the existing or new table as using print currently to check correct - thought would be a case of using a variable after the case statement AS MONTMAT - SEE BELOW..

DECLARE @COUNTER INT

SET @COUNTER = 1

DECLARE @MAT_START INT

DECLARE @MAT_END INT

WHILE @COUNTER <= (SELECT MAX(RMAT_ID) FROM ROLLING_MAT)

BEGIN

SET @MAT_START = (SELECT MAT_START FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)

SET @MAT_END = (SELECT MAT_END FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)

select DAT_YEAR_MONTH,

SUM(CASE WHEN DAT_YEAR_MONTH BETWEEN @MAT_START AND @MAT_END

THEN GWP ELSE 0 END)AS MONTHMAT

FROM MY TABLE

GROUP BY DAT_YEAR_MONTH

ORDER BY DAT_YEAR_MONTH

PRINT @MAT_START

PRINT @MAT_END

PRINT @COUNTER

SET @COUNTER= @COUNTER+1

END

It appears on searching for hours on how to do this it is not possible to use a variable to create a new column after each case step is complete?

I am trying to create a new table or update exisitng with the data which would contain the data from each case step for the mat periods with headers on each being

MAT_0

MAT_1

MAT_2

MAT_3

MAT_4...ETC.

is this possible?

any help or assistance would be great

kind regards

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Again, please read this article:

Forum Etiquette: How to post data/code on a forum to get the best help

[/url]

Follow the instructions exactly (change the data if needed to keep from sending propritary or confidential data).

Also, be sure to fully explain what you are trying to accomplish.

Do this, and you will get much better help with solving your problem.

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 8

ian.dunlop-843887 (10/7/2009)
HI, Thanks again for above- works a treat - last bit honest
I finally need to create a column for each period into the existing or new table as using print currently to check correct - thought would be a case of using a variable after the case statement AS MONTMAT - SEE BELOW..
DECLARE @COUNTER INT
SET @COUNTER = 1
DECLARE @MAT_START INT
DECLARE @MAT_END INT
WHILE @COUNTER <= (SELECT MAX(RMAT_ID) FROM ROLLING_MAT)
BEGIN
SET @MAT_START = (SELECT MAT_START FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)
SET @MAT_END = (SELECT MAT_END FROM ROLLING_MAT WHERE RMAT_ID = @COUNTER)
select DAT_YEAR_MONTH,
SUM(CASE WHEN DAT_YEAR_MONTH BETWEEN @MAT_START AND @MAT_END
THEN GWP ELSE 0 END)AS MONTHMAT
FROM MY TABLE
GROUP BY DAT_YEAR_MONTH
ORDER BY DAT_YEAR_MONTH
PRINT @MAT_START
PRINT @MAT_END
PRINT @COUNTER
SET @COUNTER= @COUNTER+1
END
It appears on searching for hours on how to do this it is not possible to use a variable to create a new column after each case step is complete?
I am trying to create a new table or update exisitng with the data which would contain the data from each case step for the mat periods with headers on each being
MAT_0
MAT_1
MAT_2
MAT_3
MAT_4...ETC.
is this possible?
any help or assistance would be great
kind regards

Works a treat? Not really... it uses a cursor which will become a performance problem sooner than later.

Why won't you spend just a little time providing the data Lynn asked for? Your life would become so much easier because most folks just won't take the time to analyze non-working code to fix an unknown problem for an unknown task.

With that in mind, the best I can offer you for your question about MAT_x is to provide a link to the following article...

http://www.sqlservercentral.com/articles/Crosstab/65048/

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