I need help creating a while loop that will update particular values and then save the results
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.
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!
@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
FROM PREPSTREAM AS tn)
SELECT CASE WHEN cte.revenueStreamID = 7
THEN ( SELECT cte2.RevenueStreamID
FROM cte AS cte2
WHERE cte2.rowID = cte.rowID + 1)
The result that I got is this:![alt text]
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
order by InvoiceIDByStore asc, RevenueStreamID desc
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] 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! :