x

I need help creating a while loop that will update particular values and then save the results

Hello all,
Please look at the RevenueStreamID column in the picture. I need help writing a code that will essentially loop through this type of process: While not at the end of file, loop through records. Whenever a 7 is found in the RevenueStreamID Column, i need to get the number directly below the 7, update the 7 to that number that is below it. Repeat this process until the end of the record is reached. I then need to put the results of the transformed table into a temp table after the loop is done. Any help would be greatly appreciated.


![alt text][1]

[1]: http://ask.sqlservercentral.com/storage/temp/182-sql-question-5-1-12.jpg
more ▼

asked May 01, 2012 at 09:34 PM in Default

cdurham gravatar image

cdurham
230 21 22 24

(comments are locked)
10|1200 characters needed characters left

3 answers: sort newest

all you have to do to put more columns in is add the column names to the start of the CTE

WITH cte (rowID,...., revenueStreamID) --insert columns where the ellipses are

then add the extra columns to the cte query

AS ( SELECT   row_number() OVER ( ORDER BY ID ) AS rowID , .... --insert extra columns where the ellipses are
                revenuestreamID
       FROM     dbo.tableName AS tn
     )

Then for your main select statement, add the columns to that

select ...., CASE WHEN cte.revenueStreamID = 7 --add columns where ellipses are

To change the name of the Case statement add as 'RevenueStreamID' after the END

ELSE cte.revenueStreamID
    END as 'revenueStreamID'

Read [this][1] to find out about CTE's.

The row_number function basically sort the query and adds sequential numbers to the query. It is the best way to solve problems like yours. http://msdn.microsoft.com/en-us/library/ms186734.aspx

The case statement simply checks when the revenuestreamID is equal to 7, and changes it to the next value.

good luck!

[1]: http://msdn.microsoft.com/en-us/library/ms190766.aspx
more ▼

answered May 02, 2012 at 11:13 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

I finally figured it out yesterday and got the rows that I needed. Once again Daniel, Thank you. You have been a very big help to me!
May 03, 2012 at 02:37 PM cdurham
(comments are locked)
10|1200 characters needed characters left

@Daniel Ross,

I ran the code that you suggested me to run and it works! I modified it a bit:

WITH cte (rowID, revenueStreamID) AS ( SELECT row_number() OVER (ORDER BY InvoiceIDByStore) AS
rowID, revenuestreamID
FROM PREPSTREAM AS tn)

SELECT CASE WHEN cte.revenueStreamID = 7
THEN ( SELECT cte2.RevenueStreamID
FROM cte AS cte2
WHERE cte2.rowID = cte.rowID + 1)
ELSE cte.revenueStreamID
END
FROM cte

The result that I got is this:![alt text][1]

This is correct, but now I need all of the columns that correspond with this. I also need to name the "no column name" as RevenueStreamID.
This is the temp table that I created before using your code
Select SipID, SaleInvoiceID, AssociationNumber, Priority, TotalPrice, TotalCost, SerialNumber, ContractNumber, ActivatedThroughPAW, DateCreatedatMidnight, ReceivedDate, InvoiceIDByStore, Location, ProductIdentifier, Description, ShortDescription, GLAccountID1, GLAccountID2, GLAccountID3, GLAccountID4, CategoryName, RevenueStreamID, RevenueType
into PREPSTREAM
from TABLE_A3
order by InvoiceIDByStore asc, RevenueStreamID desc


I included the code just in case you needed to see it, but I need all of this includinge the cte modified column. I tried to put all of this together with an into statement but I am getting errors. Thank you once again!

[1]: http://ask.sqlservercentral.com/storage/temp/185-sql-results.jpg
sql-results.jpg (18.1 kB)
more ▼

answered May 02, 2012 at 02:57 PM

cdurham gravatar image

cdurham
230 21 22 24

(comments are locked)
10|1200 characters needed characters left

is there a unique identifier in the table? to get this to work properly, you would need to have a column you can order by , then you could extract the revenueStreamID with a case statement. And use a CTE with a row_number function to get consecutive row numbers. (The unique order in the sample below is ID).

WITH    cte ( rowID, revenueStreamID )
      AS ( SELECT   row_number() OVER ( ORDER BY ID ) AS rowID ,
                    revenuestreamID
           FROM     dbo.tableName AS tn
         )
SELECT  CASE WHEN cte.revenueStreamID = 7
             THEN ( SELECT  cte2.revenueStreamID
                    FROM    cte AS cte2
                    WHERE   cte2.rowID = cte.rowID + 1
                  )
             ELSE cte.revenueStreamID
        END
FROM    cte
Then you can put the results in a temp table, or where ever you like. Just don't use a loop!
more ▼

answered May 02, 2012 at 12:05 AM

Daniel Ross gravatar image

Daniel Ross
2.9k 10 12 14

+1 for "don't use a loop"
May 02, 2012 at 11:15 AM Magnus Ahlkvist

@Daniel Ross,

I will have to sort this by invoice number. I have to then sort the revenuestream id in descending order since the 7's from each invoice is the first priority number in an invoice. I will give this code a try when I come to work today. Thank you so much!
May 02, 2012 at 11:34 AM cdurham

@Daniel Ross,

There is one more thing I forgot to mention. Could you please put comments by the cte code. I really do not understand what each part is doing. Thanks.
May 02, 2012 at 04:05 PM cdurham

@Daniel Ross

Please ignore my posting on how to insert the cte into a table. I kept working with it and figured it out, but could you please explain the with statement for me please so that i can put comments about what it does in my code? Thanks again.
May 02, 2012 at 07:00 PM cdurham
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x129
x33
x17
x6

asked: May 01, 2012 at 09:34 PM

Seen: 1883 times

Last Updated: May 03, 2012 at 03:09 PM