SQL query to calculate correct leadtime in table (MS Access)

Question

Hi, I have the following table: log(productId, status, statusDate, department...), where productId, status and statusDate is the primary key. Example:

id1 01 01/01/2009
id1 02 02/01/2009
id1 03 03/01/2009
id1 01 06/01/2009
id1 02 07/01/2009
id1 03 09/01/2009
id2 01 02/01/2009
id2 02 03/01/2009
id2 01 04/01/2009
id2 03 06/01/2009
id3 01 03/01/2009
id3 02 04/01/2009

I want to make a query that retrieves for each productId in status03, the time that has passed between the first time it reached status01 and status03.Result expected:

id1 2
id1 3
id2 4

Any idea? Thank you

Answer 1

This would work in Sql Server. You'll likely have to translate parts of it for Access:

SELECT l3.productID, DATEDIFF(d,COALESCE(l1.statusDate,l3.statusDate),l3.statusDate) AS LeadTime
FROM log l3
LEFT JOIN log l1 ON l1.productID=l3.productID AND l1.Status='01'
    AND l1.statusDate = 
      (
         SELECT TOP 1 statusDate 
         FROM log lsub 
         WHERE lsub.Status='01' AND lsub.productID=l1.productID AND l1.statusDate<l3.StatusDate
         ORDER BY statusDate DESC
      )
WHERE l3.Status='03'

Answer 2

How about:

SELECT t.ID, t.Status, t.SDateTime, 
    (SELECT Top 1 SDateTime 
     FROM t t1 WHERE t1.Status = 1 
     AND t1.ID=t.ID 
     AND t1.SDateTime<t.SDateTime 
     AND t1.SDateTime>=
         Nz((SELECT Top 1 SDateTime 
             FROM t t2 
             WHERE t2.Status=3 
             AND t2.ID=t.ID 
             AND t2.SDateTime<t.SDateTime),0)) AS DateStart, 
[SDateTime]-[DateStart] AS Result
FROM t
WHERE t.Status=3

Answer 3

I love stuff like this. Looks like it's more complicated than either of the other two answers so far have suggested. Here's a solution that will work. My apologies for the nasty formatting. Also, this will work in SQL Server, but I haven't used Access in forever, so you might need to adjust this a bit to work there. Or it may not work at all if Access doesn't support non-equijoins.

SELECT productId, MAX(tbl.TimeBetween)
FROM
 (SELECT status_1.productId as productId, status_1.statusDate as status1Date, MIN(status_3.statusDate) as status3Date, DATEDIFF(m,status_1.statusDate, MIN(status_3.statusDate)) as TimeBetween
  FROM 
  (SELECT productId, status, statusDate
    FROM log
    WHERE status = '01') status_1
  INNER JOIN
  (SELECT productId, status, statusDate
   FROM log
   WHERE status = '03') status_3
  ON status_1.productId = status_3.productId AND status_3.statusDate > status_1.statusDate
  GROUP BY status_1.productId, status_1.statusDate) tbl
GROUP BY productId, status3Date
ORDER BY productId, TimeBetween

1. The innermost selects get the records for each status.
2. Those are then joined to give the '03' status records that are greater than their corresponding '01' records (how often do you get to use a non-equijoin?)
3. They are then filtered to give the MIN '03' record that is still after the corresponding '01' record.
4. The outermost select enforces your 'first time it reached '01' status rule' since it may go to '01' status multiple times before reaching '03' status.

If someone else has a more elegant solution, I'd love to see it. I have had to write similar queries to this in the past, and I'd love to see a better solution to this type of problem.