Work in progress. Something I thought I would share.
I was asked to produce data on days required per Operation and also days between operations.
Been poking at it little by little and getting what I think I need out of it before making it something we can graph or use for time studies.
I like building my BAQ’s one bite at a time for calculated fields, then combining them. So there is plenty of excess here.
Things I’m aiming for:
- Count working days (and less holidays) required Per job Per Op.
- Count working days between Op’s
- Average and group results by similar product models.
First item I wanted to complete was calculating First Clock in and Last clock into each Operation.
MinClock
MIN(LaborDtl.ClockInDate)
MaxClock
MAX(LaborDtl.ClockInDate)
Then find the difference between them.
MinMaxClockDiff
datediff(day, MinClock, MaxClock)
Spice it up by excluding weekdays. Check if # of weeks is 1 or more, then multiply by 2 (Sat,Sun)
MinMaxClockDiffWeekdays
CASE
WHEN datediff(wk, MinClock, MaxClock) >= 1 THEN datediff(day, MinClock, MaxClock) - (datediff(wk, MinClock, MaxClock) * 2)
ELSE
datediff(day, MinClock, MaxClock)
END
Next item was to find the days between operations. So Last Clock Out of Previous Op (Row) and First Clock In of current Op (Row)
LagDiff
datediff(day, MinClock, Lag(MaxClock, 1) OVER (PARTITION BY JobHead.JobNum ORDER BY LaborDtl.OprSeq ASC) ) * -1
Exclude Weekdays again.
LagDiffWeekdays
CASE
WHEN datediff(wk, MinClock, Lag(MaxClock, 1) OVER (PARTITION BY JobHead.JobNum ORDER BY LaborDtl.OprSeq ASC) ) * -1 >= 1
THEN (datediff(day, MinClock, Lag(MaxClock, 1) OVER (PARTITION BY JobHead.JobNum ORDER BY LaborDtl.OprSeq ASC) ) * -1) - (datediff(wk, MinClock, Lag(MaxClock, 1) OVER (PARTITION BY JobHead.JobNum ORDER BY LaborDtl.OprSeq ASC) ) * -1 * 2)
ELSE
datediff(day, MinClock, Lag(MaxClock, 1) OVER (PARTITION BY JobHead.JobNum ORDER BY LaborDtl.OprSeq ASC) ) * -1
END
Starting to make some good progress. Unfortunately, data entry is commonly Completing previous Op after the next has already started. So I have to decide how to handle that with the humans or exclude those from the results we’re looking for.
Here is where i’m at, i’ll end up with pretty graphs by our product model at the end. I’m hoping the code can help someone since I spent a while reading about datediff, lag, and OVER Partition.
