Datediff needed to be used for multiple rows of order activities

Question

In SQL Server (TSQL) I need to find the date difference between a selection of order activities:

tblOrderActivity
OrderID  ActivityID  ActivityDate
1        1           4/16/2007 8:34:00 AM
1        2           4/16/2007 9:22:00 AM
2        1           4/16/2007 8:34:00 AM
3        1           4/16/2007 8:34:00 AM
3        2           4/16/2007 9:22:00 AM
4        1           4/16/2007 8:34:00 AM
4        2           4/16/2007 9:22:00 AM

Anyways... I know about DATEDIFF. So my current try at this looks like:

Select DATEDIFF( MI,
    (select tblOrderActivity.ActivityDate 
        from tblOrderActivity
        where ActivityID = 1), 
    (select tblOrderActivity.ActivityDate
        from tblOrderActivity
        where ActivityID = 2) )

This code actually works if I add AND OrderID = 1 or a specific number. The issue is... it needs to work for all Orders. So it should return the orderID as one column, and the result of subtracting ActivityID =2's date from Activity = 1's date. So for Order's 1, 3 and 4 it should have 48 minutes. For Order 2 because there isn't an activity 2, there shouldn't be a result there (I guess NULL?).

Edit:

Ok, I'm running into another issue. So this is the "full" table. There's more than two activities per order.

tblOrderActivity
OrderID  ActivityID  ActivityDate
1        1           4/16/2007 8:34:00 AM
1        2           4/16/2007 9:22:00 AM
1        3           4/16/2007 9:51:00 AM
1        4           4/16/2007 4:14:00 PM
2        1           4/16/2007 8:34:00 AM
3        1           4/16/2007 8:34:00 AM
3        2           4/16/2007 9:22:00 AM

This is what my current code looks like:

    SELECT
t1.OrderID,
DATEDIFF(MI, t1.ActivityDate, t2.ActivityDate) as TurnTime1,
DATEDIFF(MI, t2.ActivityDate, t3.ActivityDate) as TurnTime2,
DATEDIFF(MI, t3.ActivityDate, t4.ActivityDate) as TurnTime3,
DATEDIFF(MI, t1.ActivityDate, t4.ActivityDate) as TurnTime4 
FROM tblOrderActivity t1 INNER JOIN tblOrderActivity t2 ON t1.OrderID = t2.OrderID INNER JOIN tblOrderActivity t3 ON t2.OrderID = t3.OrderID INNER JOIN tblOrderActivity t4 ON t3.OrderID = t4.OrderID WHERE t1.ActivityID = 1 AND t2.ActivityID = 2 AND t3.ActivityID = 3 AND t4.ActivityID = 4

Sigh... I don't seem to be good at formatting code blocks. I apologize.

In any case. When I run this code, it only gives me the order 1, and then turn time 1,2,3,4, etc. It doesn't give me the other 2 orders because (I assume) that they don't have all 4 activities.

Is there a way to account for this?

score 0 · Answer 1 · answered Nov 04 '13 at 23:28

0

Assuming you only want the difference between ActivityID=1 and ActivityID=2, and no rows for orders with only one of the two activities:

SELECT
    t1.OrderID,
    DATEDIFF(MI, t1.ActivityDate, t2.ActivityDate)
FROM tblOrderActivity t1
INNER JOIN tblOrderActivity t2 ON t1.OrderID = t2.OrderID
WHERE t1.ActivityID = 1 AND t2.ActivityID = 2

answered Nov 04 '13 at 23:28

pobrelkey

5,853
20
29

I actually need to do the same for different activities, but it's as simple as copying and pasting and changing some numbers around. In any case, this worked. Thank you. – Jeff Chambers Nov 04 '13 at 23:34

score 0 · Answer 2 · answered Nov 05 '13 at 21:31

You have two problems here:

First, you're using inner joins. Inner joins specify that if the resulting value is not found in both tables, do not return anything. If you change all of your Inner Joins to Left Outer Joins, that will fix half of your problem, but not all of it!

Second, you're specifying each ActivityID value in the Where clause. This means it's looking at each ActivityID entry and only including it if it equals 1, 2, 3, and 4, which will never happen. Instead, you should be specifying these criteria in the join itself, like so:

Select ...
From tblOrderActivity t1
    Left Outer Join tblOrderActivity t2
        on t1.OrderID = t2.OrderID
            and t2.OrderID = 2
    Left Outer Join tblOrderActivity t3
        on t1.OrderID = t3.OrderID
            and t3.OrderID = 3
    Left Outer Join tblOrderActivity t4
        on t1.OrderID = t4.OrderID
            and t4.OrderID = 4

Where t1.OrderID = 1

You want to specify that t1.OrderID equals 1 in the where clause because that should, from your post, always be true. Remember, the overall evaluation of your Where clause must always be true. So you could change to left outer joins but not change your Where criteria and it still wouldn't work. You could change your where criteria but not your joins and it wouldn't work. You must change both!

Hope this helps!

Datediff needed to be used for multiple rows of order activities

2 Answers2