Querying the same field in a table twice

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Querying the same field in a table twice

Author

Topic

aniello35
Starting Member

1 Post

Posted - 2012-07-02 : 15:56:47

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

30421 Posts

Posted - 2012-07-02 : 16:03:32

;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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources