Sequence Number based on Date and Customer Number

Question

Sequence Number based on Date and Customer Number

8-Bit Ninja

SSC-Addicted

Points: 456
More actions
March 21, 2012 at 7:21 am

#258935

Hello..
I am trying to add a sequence number to a number of entries in my database.
my data looks like:
CustomerNumber Date
0001 21/04/2012
0001 22/04/2012
0001 27/04/2012
0002 25/04/2012
0002 30/04/2012
How can i take that data an add a simple sequence column on the end to look like:
CustomerNumber Date Sequence
0001 21/04/2012 1
0001 22/04/2012 2
0001 27/04/2012 3
0002 25/04/2012 1
0002 30/04/2012 2
I've tried various examples on the internet but i'm having trouble getting it to work. If there is a simplified method that would really help..:-)
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

aaron.reese SSChampion Points: 13498 More actions · Answer 1

RANK() OVER(Partition by CustomerNo order by CustomerNo ASC, Date ASC) as 'RANK'

Depending on what exactly you are trying to do and the data structure, you may also want to look at DENSE_RANK and NTILE which are slight variations on on the RANK function.

These are all what are known as windowed functions so there are some restrictions as to when you can use them. Look it all up on MSDN

Phil Parkin SSC Guru Points: 246962 More actions · Answer 2

aaron.reese (3/21/2012)
RANK() OVER(Partition by CustomerNo order by CustomerNo ASC, Date ASC) as 'RANK'

Or this

with x as (select CustomerNumber = '0001',

[Date] = '2012-04-21'

union

select CustomerNumber = '0001',

[Date] = '2012-04-22'

union

select CustomerNumber = '0002',

[Date] = '2012-04-25')

select *,

row_number() over (partition by CustomerNumber order by CustomerNumber, [Date])

from x

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

8-Bit Ninja SSC-Addicted Points: 456 More actions · Answer 3

Thanks for the reply...

How could i now take that script but update a new column in my database to show the sequence.

At the moment that seq column is blank but now i want to update it? I'm having trouble putting that script into an Update script? I want the seq column = RANK() OVER(Partition by SpellNumber order by SpellNumber ASC, EndDate ASC)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Instead of just having someone do the work for you, take what you have been shown and see if you can answer the question yourself. IF you have problems getting your solution to work, show us what you tried and we'll help you solve the problem.

Lowell SSC Guru Points: 323518 More actions · Answer 5

Thanks for the reply...
How could i now take that script but update a new column in my database to show the sequence.
At the moment that seq column is blank but now i want to update it? I'm having trouble putting that script into an Update script? I want the seq column = RANK() OVER(Partition by SpellNumber order by SpellNumber ASC, EndDate ASC)

i would not store this calculated sequence in the table...i think it should be calculated on demand.

for example, what if i inserted data for [Date] = '2012-01-01' , which is a date prior to whatever already exists in the existing data? what if new rows are added?

all those calculated sequence numbers would now be incorrect, and so you need a trigger or other process to start maintaining it by applying the same logic that was demonstrated.

a yucky trigger just to update something that is being calculated.

instead, you could change the process that calls the data to include the row_number/rank function, or create a view that would always be accurate on demand.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

8-Bit Ninja SSC-Addicted Points: 456 More actions · Answer 6

Alright steady on...

I've actually done that in the time it's taken for a reply... 😉 so Job is a good en!!

UPDATE TableX

SET SEQ = TB1.SEQ

FROM TableX

INNER JOIN

(SELECT CustomerNumber, Date, RANK() OVER(Partition by CustomerNumber order by CustomerNumber ASC, Date ASC) AS 'SEQ' FROM TableX) AS TB1

ON TableX.CustomerNumber = TB1.CustomerNumber AND

TableX.Date = TB1.Date

Thank you...

Dwain Camps SSC Guru Points: 86948 More actions · Answer 7

8-bit Ninja: Good on you for taking Lynn's advice and working the problem.

Just my opinion here but I think this looks a bit more elegant updating through a CTE. Try this:

DECLARE @Cust TABLE(CustomerNumber CHAR(4), [Date] DATETIME, Sequence INT)

INSERT INTO @Cust (CustomerNumber, Date)

SELECT '0001','2012-04-21'

UNION ALL SELECT '0001','2012-04-22'

UNION ALL SELECT '0001','2012-04-27'

UNION ALL SELECT '0002','2012-04-25'

UNION ALL SELECT '0002','2012-04-30'

;WITH cteCust AS (

SELECT Sequence

,RANK() OVER(Partition by CustomerNumber order by CustomerNumber ASC, Date ASC) as Seq From @Cust

)

UPDATE cteCust

SET Sequence = Seq

SELECT * FROM @Cust

CustomerNumberDateSequence

00012012-04-21 00:00:00.0001

00012012-04-22 00:00:00.0002

00012012-04-27 00:00:00.0003

00022012-04-25 00:00:00.0001

00022012-04-30 00:00:00.0002

For whatever it's worth, the query plan cost for this solution is lower than yours, probably because it doesn't join the table on itself.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

8-Bit Ninja SSC-Addicted Points: 456 More actions · Answer 8

Thanks for the Input...I'll check this method out also... 🙂

Cheers..!!