Query Help, Calculate Lag Time

[phenderson]

Hi,

I've recently inherited an older system and need to create a query.
The query will pull data from two tables:

tblA
----
prod_id -- pk, identity seed 1
prod_name

tblB
id -- pk, identity seed 1
prod_id -- fk pd.tblA
dept
dt_complete

My client wants to see how much time a specific product spent in given
department. Unfortunately, the database isn't designed quite right as
there's no field in tblB for date received. So, the client wants the
lag time to be calculated by getting the difference between the date
completed for a given department and the date completed for the
department where the product was previously. For example, if Product
001 was completed in Department YY on 11/9/2006 and completed in
Department ZZ on 11/16/2006, they want to say that it took Department
ZZ 7 days to complete Product 001.

I can provide more descriptions if necessary. Thanks in advance for
any assistance you can give.

 

[Graham Mandeno]

You can retrieve the previous dt_complete for the given product using a
subquery:

PreviousDate: (Select Max(dt_complete) from tblB as P where
P.prod_id=tblB.prod_id and P.dt_complete<tblB.dt_complete)

You can then find the difference between the two dates:

LagTime: DateDiff("d", [PreviousDate], [dt_complete])

 

[KARL DEWEY]

If your process goes through several department you might need something
different. Try these two queries that first assign an order to the
processing. Next it subtract the dates based on the next in the processint
line.

Dept_Process_Order ----
SELECT T.prod_id, T.dt_complete, T.dept, (SELECT COUNT(*) FROM [tblB] T1
WHERE T1.prod_id = T.prod_id AND T1.dt_complete <= T.dt_complete) AS Rank
FROM tblB AS T
ORDER BY T.prod_id, T.dt_complete;

SELECT Dept_Process_Order.prod_id, Dept_Process_Order_1.dept,
DateDiff("d",Dept_Process_Order.dt_complete,
Dept_Process_Order_1.dt_complete) AS [Time in Dept]
FROM Dept_Process_Order INNER JOIN Dept_Process_Order AS
Dept_Process_Order_1 ON Dept_Process_Order.prod_id =
Dept_Process_Order_1.prod_id
WHERE ((([Dept_Process_Order_1].[prod_id] &
[Dept_Process_Order_1].[Rank])=[Dept_Process_Order].[prod_id] &
[Dept_Process_Order].[Rank]+1) AND
((Dept_Process_Order_1.dt_complete)>[Dept_Process_Order].[dt_complete]))
ORDER BY Dept_Process_Order.prod_id, Dept_Process_Order.Rank;

You can retrieve the previous dt_complete for the given product using a
subquery:

PreviousDate: (Select Max(dt_complete) from tblB as P where
P.prod_id=tblB.prod_id and P.dt_complete<tblB.dt_complete)

You can then find the difference between the two dates:

LagTime: DateDiff("d", [PreviousDate], [dt_complete])

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi,

I've recently inherited an older system and need to create a query.
The query will pull data from two tables:

tblA
----
prod_id -- pk, identity seed 1
prod_name

tblB
id -- pk, identity seed 1
prod_id -- fk pd.tblA
dept
dt_complete

My client wants to see how much time a specific product spent in given
department. Unfortunately, the database isn't designed quite right as
there's no field in tblB for date received. So, the client wants the
lag time to be calculated by getting the difference between the date
completed for a given department and the date completed for the
department where the product was previously. For example, if Product
001 was completed in Department YY on 11/9/2006 and completed in
Department ZZ on 11/16/2006, they want to say that it took Department
ZZ 7 days to complete Product 001.

I can provide more descriptions if necessary. Thanks in advance for
any assistance you can give.