CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Kiera Smith
Kiera Smith

Reputation: 331

Implement Loop Logic In SQL Query

I am trying to implement some logic in an sql query to create a table similiar to:

CountNum  Identifier
20         a
40         a
60         a
20         b
40         b
20         c
20         d
40         d
20         e
40         e
60         e
80         e

As you can see, the logic is such that CountNum adds 20 to the next record as long as Identifer stays the same. if identifier changes, it starts from 20 again.

So far, I have implemented the following pseudocode:

Do the following for all records:
Update table set CountNum=20 for first Identifier
If Identifier is duplicated, add 20 to CountNum for each duplicate.

is there anyway to implement this as a query in SQL server?

I am also trying to move the results into a new table. I have tried:

INSERT INTO tblPayments select Identifier AS PaymentsIdentifier
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY Identifier)*20 CountNum
FROM dbo.oldPayments

However it generates the error:

Column name or number of supplied values does not match table definition.

Upvotes: 2

Views: 121

Answers (1)

Lamak
Lamak

Reputation: 70638

No need for a loop at all, you can use ROW_NUMBER():

SELECT Identifier,
       ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY Identifier)*20 CountNum
FROM dbo.YourTable;

Upvotes: 5

Related Questions