0
\$\begingroup\$

Here's my example set up in rextester.

https://rextester.com/LROA87361

I have the following table in gray (the rightmost 2 columns are for explanation).

enter image description here

As you can see an employee's time entries can overlap. This is sorted by LaborStart from latest to most recent.

I want to flag those overlapping time entries and correctly calculate the time worked without any overlaps.

I managed to do what but had to resort to 2 subqueries and 2 helper columns.

enter image description here

Here's my code.

SELECT
Y.*

,(CASE WHEN Y.NewEndTimeLag > Y.NewEndTime
   THEN 0

   WHEN Y.LaborStart <= Y.NewEndTimeLag
   THEN DATEDIFF([MINUTE], Y.NewEndTimeLag, Y.NewEndTime)

   ELSE DATEDIFF([MINUTE], Y.LaborStart, Y.NewEndTime)
   END)    AS [New Time Spent] 


,(CASE WHEN Y.LaborStart < Y.NewEndTimeLag
   THEN 1

   ELSE 0 END) AS [Time Overlap? (1=Yes 0=No)]


FROM
(

  SELECT 
  X.*
  , LAG(X.NewEndTime,1) OVER (PARTITION BY X.Employee ORDER BY X.Employee, X.LaborStart) AS [NewEndTimeLag]

  FROM (
                    SELECT

                        IncidentNumber
                        , Employee
                        , LaborStart
                        , LaborEnd  


                        , (CASE WHEN LaborEnd <= LAG(LaborEnd,1,LaborEnd) OVER (PARTITION BY Employee, MONTH(LaborStart), DAY(LaborStart)  ORDER BY Employee, LaborStart)
                    THEN LAG(LaborEnd,1,LaborEnd) OVER (PARTITION BY Employee, MONTH(LaborStart), DAY(LaborStart)  ORDER BY Employee, LaborStart) 
                    ELSE LaborEnd END ) AS [NewEndTime]


                    FROM [WorkEntries]

                    GROUP BY
                    IncidentNumber
                    , Employee
                    , LaborStart
                    , LaborEnd
  ) X


) Y


ORDER BY
Y.Employee
,Y.LaborStart

I welcome any advice on improving this query or the logic behind it!

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.