SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Querying the same field in a table twice		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aniello35
Starting Member

USA
1 Posts
Posted - 07/02/2012 :  15:56:47  Show Profile  Reply with Quote
I am trying to subtract the time from where status = Delivered from the status = Picked Up. All the data is in the same table. Let’s call it table1 and I want to return the values: PTN, NAME and the time difference. Is this possible to do?

PTN NAME DATE STATUS
11014419 Joe Dickson 2012-06-25 14:55:58 Delivered
11014419 Mike Draia 2012-06-25 14:28:17 Loaded
11014419 Bob Geber 2012-06-25 13:14:31 Received
11014419 Bob Geber 2012-06-25 13:14:31 Picked Up
11014419 Bob Geber 2012-06-25 13:14:31 Printed

SwePeso
Patron Saint of Lost Yaks

Sweden
29156 Posts
Posted - 07/02/2012 :  16:03:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
;WITH cteSource(PTN, Name, PickedUp, Delivered)
AS (SELECT PTN, Name, MIN(CASE WHEN Status = 'Picked Up' THEN [Date] ELSE NULL END) AS PickedUp,
MAX(CASE WHEN Status = 'Delivered' THEN [Date] ELSE NULL END) AS Delivered
FROM dbo.Table1 GROUP BY PTN, Name)
SELECT PTN, Name, DATEDIFF(SECOND, PickedUp, Delivered) AS TimeDifferenceInSeconds
FROM cteSource


N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000