StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODatediff needed to be used for multiple rows of order activities
text
Body
copied!<p>In SQL Server (TSQL) I need to find the date difference between a selection of order activities: </p> <pre><code>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 </code></pre> <p>Anyways... I know about DATEDIFF. So my current try at this looks like:</p> <pre><code>Select DATEDIFF( MI, (select tblOrderActivity.ActivityDate from tblOrderActivity where ActivityID = 1), (select tblOrderActivity.ActivityDate from tblOrderActivity where ActivityID = 2) ) </code></pre> <p>This code actually works if I add <code>AND OrderID = 1</code> 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?).</p> <p>Edit: </p> <p>Ok, I'm running into another issue. So this is the "full" table. There's more than two activities per order. </p> <pre><code>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 </code></pre> <p>This is what my current code looks like:</p> <pre><code> 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 </code></pre> <p>Sigh... I don't seem to be good at formatting code blocks. I apologize. </p> <p>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. </p> <p>Is there a way to account for this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload