Re: DateDiff problem

From: Steve Kass (skass_at_drew.edu)
Date: 11/07/04 

Date: Sun, 07 Nov 2004 11:29:04 -0500

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

Strugglin' wrote:

>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
>column. Is this even possible?
>
>