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.