0

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

Improve this question
4
  • Hi, Welcome to the site. Do you get an error when running that code? If the select works the insert should probably work too. Is ID defined as an identity column? If so consider setting IDENTITY_INSERT
    – Tom V
    Commented Nov 12, 2018 at 8:04
  • Hey Tom. Thanks for the reply. I don't get an error. In fact, it says that 704 rows were affected. However the column is empty (NULL). I originally had this column set up as Identity column populated with sequential numbers. But what I want to do is reset the number on change in year_,month_,date_,salesrep. I included a small example of the data,however, there are multiple columns beyond what I have shown that reflects RevenueYTD, Yearly_Quota...etc. I changed the ID field to just datatype INT but the insert/select query does not populate this column. I'm in a bind to get this right!
    – Marlene
    Commented Nov 12, 2018 at 12:40
  • 1
    I figured it out. I've never used Row_Number() so I had to google what the heck I was doing wrong. I just created a new table since that's what the example from my google search was doing. Thanks for your help. This worked: [SELECT ROW_NUMBER() OVER (PARTITION BY Year_,Month_,Date_,SalesRep ORDER BY SalesRep) - 1 As ID,* INTO Sales_Info_Snapshot_WD_TEST2 FROM Sales_Info_Snapshot_WD_TEST where date_ = '10/31/2018'
    – Marlene
    Commented Nov 12, 2018 at 13:04
  • @Marlene Seeing as you found a solution that works, you might want to post it as an answer. Self-answering is encouraged and sometimes provides lasting value for the community.
    – John K. N.
    Commented Nov 13, 2018 at 6:39

1 Answer 1

Reset to default
1

So it was suggested that I post the answer I was able to come up with to resolve my issue. I decided to create a new table to make sure it would work and then save that table as the original table.

SELECT
*, ROW_NUMBER() OVER(PARTITION BY YEAR_,MONTH_,DATE_,SALESREP 
ORDER BY   SALESREP)-1 AS DEL_ID INTO

SALES_INFO_SNAPSHOT_TEMP FROM SALES_INFO_SNAPSHOT_TEST
WHERE DATE_ = '10/31/2018'

This did exactly what I wanted. A column with sequential numbers beginning with 0 and was inserted with existing data into a new table. The sequential numbers restarted at each change in year_,month_,date_,salesrep.

This allowed me to determine which of four duplicate rows per SalesRep to be deleted. There were over a thousand rows in all for date_ = '10/31/2018' so I wanted to get this right the first time.
So I was able to determine which row to salvage (the same for each SalesRep) and send a DELETE statement. It was determined to salvage where ID = '1'.

DELETE FROM SALES_INFO_SNAPSHOT_TEMP WHERE DATE_ = '10/31/2018' AND ID IN ('0','2','3')

Worked like a charm :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.