CODEWITHSUNDEEP
sqlsql-servert-sql
lets_go_bowling
lets_go_bowling

Reputation: 13

Generate Sequential IDs for Line Items on a Per Customer Basis in SQL Server

Apologies if the title is difficult to understand... it proved challenging to summarize my question.

The business has created a self-reporting spreadsheet to give out to our stores to track customer leads. Each row represents one follow-up action, therefor each customer can have multiple rows. Employees were not properly filling out the follow-up counter column (e.g. they'd forget they already spoke to somebody or someone else followed up a previous lead so one customer would have two rows labeled as follow-up #1) so we eliminated it and I'm now using a concatenation of date and sequential rowID column to order their interactions.

I would like to be able to derive follow-up #s with this data. I am now able to easily order the data by Customer, Date, and RowID to get an accurate flow of follow-ups, but I would like to now number those rows 1 through X so I can perform analysis and aggregate on the follow-up counter.

Simplified example of customers ordered with their Date/ID:

Please click link for example 1 (not enough rep to embed yet)

How could I then, with the data above, create the following:

Please click link for example 2 (not enough rep to embed yet)

I'd love to be able to derive the follow-up counters after the fact via SQL or T-SQL. Any ideas? Thank you.

Upvotes: 1

Views: 50

Answers (1)

Eric Brandt
Eric Brandt

Reputation: 8101

Take a look at Window Functions, particularly ROW_NUMBER.

I believe you're looking for something like this:

SELECT
 [Customer Name],
 [date_id_concat],
 ROW_NUMBER() OVER (PARTITION BY [Customer Name] ORDER BY [date_id_concat]) AS InteractionSeq
FROM
 TABLE;

Upvotes: 3

Related Questions