sql server mseq: I have a db with a table that records details about order transactions. The
main columns I am concerned about are the "Transacton_Type" column and the
"Date_Time" column. Each order has more than one transaction/rows in the
table (ie. Pending, Shipped, etc...), with each transaction having its' own
time stamp (as opposed to a table with a Pending column and a Shipped
column). How can I use the DateDiff function to give me the difference
between the Pending date and Shipped date for each Order (uniquely referenced
in a "P.O." column) if the timestamp for each transaction exists in the same

There are a couple of ways to do this, and they should be efficient if
there are supporting indexes:

select
T1.[P.O.],
T1.Date_Time as PendingDate,
T2.Date_Time as ShippedDate,
datediff(day, T1.Date_Time, T2.Date_Time) as LagTime
from yourTable T1
join yourTable T2
on T1.[P.O.] = T2.[P.O.]
where T1.Transaction_Type = ''Pending'
and T2.Transaction_Type = 'Shipped'
-- add where clause to restrict to a certain customer, for example.

The second way shows the pending, shipped, etc., dates as separate
columns. You can create the inner query here as a view, or adapt this
in other ways for individual queries.

select [P.O.], datediff(day,PendingDate, ShippedDate) as LagTime
from (
select
[P.O.],
max(case when Transaction_Type = 'Pending' then Date_Time end) as
PendingDate,
max(case when Transaction_Type = 'Shipped' then Date_Time end) as
ShippedDate,
.. other dates you need
from yourTable
-- restrict to a P.O. either here or ..
group by [P.O.]
) T
-- .. here (other place to restrict for limited results)

Steve Kass
Drew University

[quoted text, click to view]