October 29, 2012 at 7:16 am
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
October 29, 2012 at 7:24 am
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
October 29, 2012 at 9:05 am
Thanks. This is perfect!
Thanks also for the posting guidelines. I hadn't realized how everyone was able to get nicely ordered code into here on their posts
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply