I am attempting to populate an Identity column with sequential numbers but I want the count to start over when the value of a key field changes.
Example data and desired ID column results:
Month_ Date_ Year_ SalesRep ID 10 2018-10-31 2018 AL01 0 10 2018-10-31 2018 AL01 1 10 2018-10-31 2018 AL01 2 10 2018-10-31 2018 AL01 3 10 2018-10-31 2018 AL02 0 10 2018-10-31 2018 AL02 1 10 2018-10-31 2018 AL02 2 10 2018-10-31 2018 AL02 3
The below query does not produce the desired results:
INSERT INTO SALES_INFO_SNAPSHOT_WD_TEST(ID) SELECT DEL_ID FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Year_,Month_,Date_,SalesRep ORDER BY SalesRep) - 1 As DEL_ID FROM Sales_Info_Snapshot_WD_TEST where date_ = '10/31/2018') D WHERE DEL_ID IS NOT NULL
Anyone know of a painless solution?
The select query works perfectly but does not insert into the table.
I need to number these rows in order to identify a large chunk of data that needs to be deleted from a very important reporting table.
Thank you. Any help is appreciated.
Marlene SSMS v 17.7