Query Help, Calculate Lag Time

P

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.
 
G

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])
 
K

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;


Graham Mandeno said:
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

phenderson said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top