Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Return rows with youngest DateTime Expand / Collapse
Author
Message
Posted Monday, October 29, 2012 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:29 AM
Points: 10, Visits: 23
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
Post #1378236
Posted Monday, October 29, 2012 7:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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)


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1378240
Posted Monday, October 29, 2012 9:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:29 AM
Points: 10, Visits: 23
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
Post #1378296
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse