Solved

# T-SQL Challenge

Posted on 2005-05-11
1,169 Views
I have two tables, shipping(parent) and shippingStatus(Child). Sample Data:

shipping:
shipID, packageType, Date,

1, box, jun 5
2,  letter, jun 5

shippingStatus:
shipStatusID, shipID, shipStatus, GetDateTime()
1,1, scanned, jun5 12AM
2,1, in-route, jun5  1AM
3,1, in-route, jun5 1AM
4,1, delivered, jun5 7AM
5,2, scanned, jun5 12AM
6,2, in-route, jun5  1AM
7,2, in-route, jun5 1AM
8,2, in-route, jun5 7AM

How do i figure out the time between 2 statuses where the statuses are parameters.

For example I want the time difference when it was scanned to when it was first in-route for the box? example output:
PackageType, FirstOrdinal, SecondOrdinal, TimeDiff
Box, jun5 12AM, Jun5 1 AM, 1 hour diff
0
Question by:vinny45

LVL 28

Accepted Solution

Try this:

SELECT S.PackageType, A.GetDateTime AS FirstOrdinal, B.GetDateTime AS SecondOrdinal,
DATEDIFF(H, A.GetDateTime, B.GetDateTime) AS TimeDiff
FROM Shipping S INNER JOIN (
SELECT ShipID, MIN(GetDateTime) AS GetDateTime
FROM ShippingStatus
WHERE ShipStatus = 'scanned'
GROUP BY ShipID) A
ON S.ShipID = A.ShipID
INNER JOIN (
SELECT ShipID, MIN(GetDateTime) AS GetDateTime
FROM ShippingStatus
WHERE ShipStatus = 'in-route'
GROUP BY ShipID) B
ON A.[ShipID] = B.[ShipID]
0

LVL 34

Expert Comment

First I'm a little confused by the "GetDateTime()" as a field name so I'll just call it StatusDate

SELECT Shipping.PackageType,
Scanned.StatusDate AS ScanDate,
InRoute.StatusDate AS InRouteDate,
Delivered.StatusDate AS DeliveredDate,
DATEDIFF(hour, InRoute.StatusDate, Scanned.StatusDate) AS InRouteTime,
DATEDIFF(hour, Delivered.StatusDate, InRoute.StatusDate) AS DeliveryTime,
FROM Shipping
INNER JOIN shippingstatus Scanned
ON Shipping.shipID = Scanned.shipID
AND Scanned.shipStatus = 'scanned'
LEFT OUTER JOIN shippingstatus InRoute
ON Shipping.shipID = InRoute.shipID
AND InRoute.shipStatus = 'in-route'
LEFT OUTER JOIN shippingstatus Delivered
ON Shipping.shipID = Delivered.shipID
AND Delivered.shipStatus = 'delivered'

There is probably some NULL checking that needs to be handled in the SELECT portion...
0

LVL 34

Expert Comment

correction...i forgot to take into account the multiple 'in-route' records

SELECT Shipping.PackageType,
Scanned.StatusDate AS ScanDate,
InRoute.StatusDate AS InRouteDate,
Delivered.StatusDate AS DeliveredDate,
DATEDIFF(hour, InRoute.StatusDate, Scanned.StatusDate) AS InRouteTime,
DATEDIFF(hour, Delivered.StatusDate, InRoute.StatusDate) AS DeliveryTime,
FROM Shipping
INNER JOIN shippingstatus Scanned
ON Shipping.shipID = Scanned.shipID
AND Scanned.shipStatus = 'scanned'
LEFT OUTER JOIN (SELECT ShipID, Max(StatusDate) FROM shippingstatus WHERE ShipStatus = 'in-route' GROUP BY ShipID) AS InRoute
ON Shipping.shipID = InRoute.shipID
LEFT OUTER JOIN shippingstatus Delivered
ON Shipping.shipID = Delivered.shipID
AND Delivered.shipStatus = 'delivered'
0

LVL 28

Expert Comment

BriCrowe, you should be using the MIN(StatusDate) because they need the first in-route status.
0

LVL 68

Expert Comment

DECLARE @status1 VARCHAR(30) --<<-- change as needed
DECLARE @status2 VARCHAR(30) --<<-- change as needed

SET @status1 = 'scanned' --<<-- change as needed or input param in sp
SET @status2 = 'in-route' --<<-- change as needed or input param in sp

SELECT s.packageType, ss.MinStatus1, ss.MinStatus2,
CAST(DATEDIFF(MINUTE, ss.MinStatus1, ss.MinStatus2) / 60.0 AS DECIMAL(4,1)) AS [TimeDiff (Hours)]
FROM shipping s
INNER JOIN (
SELECT shipID,
MIN(CASE WHEN status = @status1 THEN GetDateTime ELSE '19000101' END) AS MinStatus1,
MIN(CASE WHEN status = @status2 THEN GetDateTime ELSE '19000101' END) AS MinStatus2
FROM shippingStatus
GROUP BY shipID
-- only select shipments that have rows in both statuses  --<<--chg if needed
HAVING MIN(CASE WHEN status = @status1 THEN GetDateTime ELSE '19000101' END) > '19000101'
AND MIN(CASE WHEN status = @status2 THEN GetDateTime ELSE '19000101' END) > '19000101'
) AS ss ON s.shipID = ss.shipID
0

LVL 68

Expert Comment

That approach requires only one pass thru the shippingStatus table: multiple subqueries or joins will require multiple scans of the table.
0

LVL 68

Expert Comment

To vinny45 (requestor):

Be careful when using DATEDIFF(HOUR, ...): DATEDIFF only checks boundaries, so, for example, the time difference between 12:59 PM and 2:00 PM will show as 2 hours (!).
0

Author Comment

rafrancisco examplee worked, thanks all, maybe i couldn't understand the others. sorry for my shortcomings
0

## Featured Post

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.