Implement Loop Logic In SQL Query

Question

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.

Is this a newly generated table? Are you pulling the Identifier from an existing table? — TTeeple
– TTeeple, Commented May 22, 2015 at 15:21
yes, identifier is selected from an existing table to update this newly generated table. — Kiera Smith
– Kiera Smith, Commented May 22, 2015 at 15:26

Lamak · Accepted Answer · 2015-05-22 15:21:30Z

5

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;

answered May 22, 2015 at 15:21

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kiera Smith Over a year ago

Excellent! Works perfectly

Collectives™ on Stack Overflow

Implement Loop Logic In SQL Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related