I’ve got a query that can do the calculation… I think. Since we want to be doing split burden for everything this would work in our situation.
The ? parameter variables at the top are something that I will provide to the query when I’m running it. At the very bottom it spits out the BurdenHrs.
If any of the SQL wizards on here have any ideas let me know. It was quite an academic endeavor to come up with this and I definitely levelled up my SQL.
DECLARE @Company AS NVARCHAR(8) = ?
, @EmployeeNum AS NVARCHAR(8) = ?
, @LaborHedSeq AS INT = ?
, @LaborDtlSeq AS INT = ?
, @EndDateTime AS DATETIME = ?
, @Shift AS INT = ?
, @BurdenMinutes AS DECIMAL(10, 2) = 0
, @JobsOpenThatMinute AS INT = 0
;
DECLARE MinutesJobsCursor CURSOR FOR
-- List of all minutes in yyyy-mm-dd hh:mm:ss from the start of the job until @EndDateTime
WITH cte_minute_series (MyMinute) AS (
SELECT CONVERT(DATETIME, CONCAT(ld.ClockInDate, ' ', ld.DspClockInTime)) AS MyMinute
FROM dbo.LaborDtl AS ld WITH (NOLOCK)
WHERE ld.Company = @Company
AND ld.LaborHedSeq = @LaborHedSeq
AND ld.LaborDtlSeq = @LaborDtlSeq
UNION ALL
SELECT DATEADD(MINUTE, 1, MyMinute)
FROM cte_minute_series
WHERE MyMinute < @EndDateTime
)
-- Gets a result set where each row is a count of open jobs this employee was in at each minute of the time period defined by the cte above
, cte_jobs (MyMinute, OpenJobCount) AS (
SELECT ms.MyMinute
, COUNT(ld.JobNum) AS OpenJobCount
FROM cte_minute_series AS ms
INNER JOIN dbo.LaborDtl AS ld WITH (NOLOCK)
ON (ld.DspClockinTime <= FORMAT(ms.MyMinute, 'HH:mm') AND
ld.DspClockOutTime >= FORMAT(ms.MyMinute, 'HH:mm') AND
ld.ClockInDate = CONVERT(DATE, @EndDateTime) AND
ld.Company = @Company AND
ld.EmployeeNum = @EmployeeNum AND
ld.LaborType IN ('P', 'S'))
WHERE
-- Don't Count Breaks or Lunch
NOT EXISTS (SELECT *
FROM erp.ShiftBrk AS breaks WITH (NOLOCK)
WHERE breaks.Company = @Company
AND breaks.Shift = @Shift
AND FORMAT(ms.MyMinute, 'HHmm') >= breaks.DspBreakStart
AND FORMAT(ms.MyMinute, 'HHmm') <= breaks.DspBreakEnd)
AND NOT EXISTS (SELECT *
FROM erp.JCShift AS lunch WITH (NOLOCK)
WHERE lunch.Company = @Company
AND lunch.Shift = @Shift
AND FORMAT(ms.MyMinute, 'HHmm') >= lunch.DspLunchStart
AND FORMAT(ms.MyMinute, 'HHmm') <= lunch.DspLunchEnd)
GROUP By ms.MyMinute
)
-- The result set of cte_jobs is for the MinutesJobsCursor
SELECT OpenJobCount
FROM cte_jobs
-- Open the newly minted cursor and loop through it
OPEN MinutesJobsCursor;
FETCH NEXT FROM MinutesJobsCursor INTO @JobsOpenThatMinute;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Accumulator for Burden minutes. Add the correct fraction of a minute based on how many jobs were open that minute
SET @BurdenMinutes = @BurdenMinutes
+ ( 1 / CONVERT(DECIMAL(10,2), COALESCE(NULLIF(@JobsOpenThatMinute,0), 1)) )
FETCH NEXT FROM MinutesJobsCursor INTO @JobsOpenThatMinute
END;
CLOSE MinutesJobsCursor;
DEALLOCATE MinutesJobsCursor;
-- Finally divide the minutes by 60 to get the BurdenHrs that will go onto the LaborDtl Record
SELECT CONVERT(DECIMAL(10, 2), (@BurdenMinutes / 60)) AS BurdenHrs
I wonder if someone wants to run this through the fancy SQL to BAQ tool to see what it does that might be fun.
I think it could be modified further to join in the resource group of the job and only count jobs that are split burden but that’s an exercise for another time / place.