jagnew (10/29/2012)
I have a dataset for a ticketing system. The dataset contains the Ticket #, Assigned Technician, and the DateTime they were scheduled on that ticket.Given this information, for each Ticket # I need to return the assigned tech who was last scheduled. Eg Below
Dataset
Ticket | Tech | Date
10000 | Tech 1 | 2012-09-20 01:00:00:000
10000 | Tech 2 | 2012-09-25 01:00:00:000
10200 | Tech 1 | 2012-09-10 12:00:00:000
10200 | Tech 2 | 2012-09-15 12:00:00:000
Given the above dataset the required result is
Ticket | Tech | Date
10000 | Tech 2 | 2012-09-25 01:00:00:000
10200 | Tech 2 | 2012-09-15 12:00:00:000
Add this to your SELECT:
rn = ROW_NUMBER() OVER(PARTITION BY Ticket ORDER BY Date DESC)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden