CODEWITHSUNDEEP
sqlsql-serverduplicatessql-server-2014gaps-and-islands
Royal
Royal

Reputation: 13

Alternate Numbering rows if the data is the different

Current Data:

|LotNumber |ItemNumber |ItemName
|76        |1          |WidgetRight
|76        |1          |WidgetLeft
|76        |2          |WidgetRight
|76        |2          |WidgetLeft
|76        |7          |WidgetRight
|76        |7          |WidgetLeft
|76        |9          |WidgetRight
|76        |10         |WidgetRight
|76        |10         |WidgetLeft
|80        |3          |WidgetRight
|80        |3          |WidgetLeft
|80        |7          |WidgetRight
|80        |7          |WidgetLeft
|80        |11         |WidgetRight
|80        |31         |WidgetRight

Need to add a column that alternates between 1 and 2 every time the ItemNumber changes

|LotNumber |ItemNumber |ItemName       |NewField
|76        |1          |WidgetRight    |1
|76        |1          |WidgetLeft     |1
|76        |2          |WidgetRight    |2
|76        |2          |WidgetLeft     |2
|76        |7          |WidgetRight    |1
|76        |7          |WidgetLeft     |1
|76        |9          |WidgetRight    |2
|76        |10         |WidgetRight    |1
|76        |10         |WidgetLeft     |1
|80        |3          |WidgetRight    |2
|80        |3          |WidgetLeft     |2
|80        |7          |WidgetRight    |1
|80        |7          |WidgetLeft     |1
|80        |11         |WidgetRight    |2
|80        |31         |WidgetRight    |1

Current Code not working ...

DECLARE @LastLine Varchar(1)

SELECT ItemNumber, ItemName,
CASE WHEN ItemNumber <> LAG (ItemNumber, 1, 0) OVER (PARTITION BY LotNumber ORDER BY LotNumber, ItemNumber )
THEN
    CASE WHEN @LastLine = '1'
    THEN  '2'
    ELSE  '1'
    END 
END AS LastLine

Thank you for any help.

Upvotes: 1

Views: 50

Answers (1)

GMB
GMB

Reputation: 222572

You can just use dense_rank() and arithmetics:

select t.*,
    1 + (dense_rank() over(order by lotnumber, itemnumber) + 1) % 2 as newfield
from mytable t

Demo on DB Fiddle

Upvotes: 5

Related Questions