Cycle Time calc 3

[rjoubert]

I have a history (audit trail) table which tracks steps in a workflow process and associated times for each step.

ID, StepID, PersonID, DateTime
1, 1, 22, 07/15/2006
1, 2, 19, 07/18/2006
1, 3, 6, 07/22/2006
2, 1, 23, 07/19/2006
2, 2, 4, 07/20/2006
2, 3, 5, 07/21/2006

I want to show a report that calculates the cycle time for each ID, grouped by PersonID and StepID.

Person 22
Step 1
ID: 1 Cycle Time: x Days

Now the painful thing is that I spent several days creating some VBA code to do this for me, and after a miscommunication with the users, I deleted the code. What I would like to know is if anyone has done this WITHOUT USING VBA CODE. I know how to do it with VBA...it's just don't that on a Friday, I don't feel like starting it all over again. Has anyone done this with some creative queries?

Thanks.

 

[Golom]

The critical question (for me at least) is "How is cycle time computed?" Grouping by PersonID, StepID and ID would produce exactly one record in each group for the sample data you have provided. I assume (but don't really know) that cycle time involves taking a difference between dates in the DateTime field but exactly which ones isn't apparent.

 

[rjoubert]

Oops...forgot to include that important piece of info. The DateTime field reflects when the workflow entered that particular step. So, ID 1 entered Step 1 on 7/15/2006. It stayed in Step 1 until it moved into Step 2 on 7/18/2006, so the Cycle Time would be 3 days.

 

[Golom]

Here's one possibility

Select A.ID, B.StepID, B.PersonID, 
       DateDiff("d", A.DateTime, B.DateTime) As [Cycle Time]

From tblAudit A INNER JOIN tblAudit B
     ON     A.ID         = B.ID
        AND A.StepID + 1 = B.StepID

Order By A.ID, B.StepID

Since "PersonID" is unique on every record there doesn't seem to be much point in filtering on it.

 

[IT4EVR]

I did something like this:

SELECT a.ID, a.StepID, a.PersonID, a.StepDate- (SELECT b.StepDate FROM Table1 AS b WHERE b.ID = a.ID AND a.StepID + 1= b.StepID) AS CycleTime
FROM Table1 AS a
GROUP BY a.PersonID, a.StepID, a.ID, a.StepDate

 

[rjoubert]

Thanks for the replies guys. Allow me to throw a couple of little monkey wrenches into the works.

First, the StepIDs are not necessarily sequential. I should've posted a better example of the data. Actually, the StepIDs are in a lookup table and range anywhere from 3000 to 3021. The first step in the workflow could have StepID of 3015, the next could be 3009.

Second, there could be two records with the same StepID but different PersonID, meaning the item was reassigned but stayed in the same step.

ID, StepID, PersonID, DateTime
1, 3021, 12, 07/16/2006
1, 3021, 14, 07/17/2006
1, 3015, 33, 07/19/2006

 

[Golom]

Presumably then, the only way to determine the order of the steps is to look at the DateTime field since StepID and/or PersonID do not bear any numerical relationship to one another that defines which is first and which is second (or third, or fourth, etc.)

Select A.ID, B.StepID, B.PersonID, 
       DateDiff("d", A.DateTime, B.DateTime) As [Cycle Time]

From tblAudit A INNER JOIN tblAudit B
     ON A.ID = B.ID

Where B.DateTime = (Select MIN(DateTime) From tblAudit X
                    Where X.DateTime > A.DateTime
                      AND X.ID = A.ID)
                    

Order By A.ID, B.StepID

Of course, if the DateTime field can contain duplicates then there is no way to determine the order of the steps.

 

[rjoubert]

So close...Here's the raw data for one ID...

ID, StepID, PersonID, DateTime
1, 3001, 1018, 7/18/2006 1:36:33 PM
1, 3000, 1032, 7/23/2006 11:27:57 AM
1, 3011, 1018, 7/24/2006 2:18:30 PM
1, 3018, 1015, 7/24/2006 2:29:34 PM

Here's what I get from your query...

ID, StepID, PersonID, CycleTime
1, 3000, 1032, 5
1, 3011, 1018, 1
1, 3018, 1015, 0

I would expect to see...
ID, StepID, PersonID, CycleTime
1, 3001, 1018, 5
1, 3000, 1032, 1
1, 3011, 1018, 0
1, 3018, 1015,

Your query seems to be shifting down by one record. But it's getting close. I certainly appreciate your efforts.

 

[IT4EVR]

This is what I did and not sure if it works for you. I did 3 separate queries. Two queries are joined into a final result set. It involves generating running counts on groups of records.

If someone has a better way to do this by all mean share...

qry_Setup_1

SELECT a.ID, a.StepID, a.StepDate, (SELECT COUNT(*) FROM tblAudit AS b WHERE b.ID = a.ID AND b.StepDate < a.StepDate) AS RunningCount, a.PersonID
FROM tblAudit AS a;

qry_Setup_2

SELECT a.ID, a.StepID, a.StepDate, (SELECT COUNT(*) FROM tblAudit AS b WHERE b.ID = a.ID AND b.StepDate < a.StepDate)+1 AS RunningCount, a.PersonID
FROM tblAudit AS a;

qry_Final_Result

SELECT a.ID, a.StepID, a.PersonID, DateDiff("d",a.StepDate,b.StepDate) AS CycleTime
FROM qry_Setup_1 AS a LEFT JOIN qry_Setup_2 AS b ON (a.ID=b.ID) AND (a.RunningCount=b.RunningCount);

 

[IT4EVR]

This is assuming you are sorting the records as you want them before hand.

 

[Golom]

To get the records that you want, try

Select A.ID, [COLOR=red][b]A[/b][/color].StepID, [COLOR=red][b]A[/b][/color].PersonID, A.DateTime,
       DateDiff("d", A.DateTime, B.DateTime) As [Cycle Time]

From tblAudit A INNER JOIN tblAudit B
     ON A.ID = B.ID

Where B.DateTime = (Select MIN(DateTime) From tblAudit X
                    Where X.DateTime > A.DateTime
                      AND X.ID = A.ID)
                    
UNION ALL

Select A.ID, A.StepID, A.PersonID, A.DateTime, NULL

From tblAudit A

Where A.DateTime = (Select MAX(X.DateTime) From tblAudit X
                    Where X.ID = A.ID)

Order By 1, 4

The UNION ALL just appends the last record for each ID. The first SELECT will not include it because there is no record that follows that last one.

 

[rjoubert]

IT4EVR, your solution had the records shifted up by one, but it was close. I appreciate your efforts.

Golom, your last post did the trick. Thanks a bunch!

 

[IT4EVR]

GOLOM,

I give my star to you. I think your solution is superior. Good job.