Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am interested in producing the following view where different delivery dates are numbered.

SalesID    Delivery Date DateNo
SO00000014 2014-02-21    1
SO00000015 2014-02-13    1
SO00000016 2014-01-28    1
SO00000017 2014-03-17    1
SO00000018 2014-02-13    1
SO00000020 2014-03-19    1
SO00000020 2014-03-20    2
SO00000020 2014-03-21    3

My question is how to number different dates associated to a single SalesId like in the SO00000020 above. I am using SQL Server 2008.

share|improve this question
    
Each record would contain the total number of sales per sales person up to that point? –  Mark Wilkinson Apr 22 at 11:47
1  
Only you can really answer your own question. What column combinations require a unique result that consumers (applications, etc.) and business demand? SalesID + DateNo? SalesID + Delivery Date + DateNo? –  Thomas Stringer Apr 22 at 11:58

1 Answer 1

up vote 5 down vote accepted

Not knowing your data, the view definition would be:

SELECT
    [SalesID],
    [Delivery Date],
    ROW_NUMBER() OVER (PARTITION BY [SalesID] ORDER BY [Delivery Date]) AS DateNo
FROM
    [WhatEverTable]

http://technet.microsoft.com/en-us/library/ms186734(v=sql.105).aspx

ROW_NUMBER() by itself will return a running count of the rows, but when you add the OVER(PARTITION BY [SalesID] ORDER BY [Delivery Date]) it gives a running count per sales id, ordered by the date.

Does that make sense?

EDIT: If you do make this a view, just be aware that the results will be filtered by your WHERE clause. Using your data above, if you queried for sales that occurred after 2014-03-20, SalesID SO00000020 will only have a single entry for 2014-03-21 with a DateNo of 1.

If you need the data to persist regardless of the WHERE clause, you will likely just have to scrap the idea of a view and get creative with the query that accesses the data.

share|improve this answer
    
Excellent Mark, that's it. Thanks! I was fiddling with row_number but I couldn't get it right. –  V509 Apr 22 at 12:56
    
Glad I could help! Make sure to mark this as answered so others know this worked for you. –  Mark Wilkinson Apr 22 at 13:31
    
Where do I mark this as answered? I don't have a full account yet. Would that hold me back from marking it as answered? –  V509 Apr 22 at 13:41
    
To the left of the answer (just under the vote count) you should see a grey check mark, just click it to mark this question as answered. –  Mark Wilkinson Apr 22 at 13:49
1  
If you turn your query there into a sub-select then you don't need to worry about filtering... –  Ben Apr 22 at 14:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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