How to Calculate the Total Unique Days Employed for All Jobs - No overlap days counted twice-CodePudding

/* Data Setup */
DROP TABLE IF EXISTS #DaysPerJob;
CREATE TABLE #DaysPerJob
(
    GroupID INT, JobDesc VARCHAR(100), StartDate DATE, EndDate DATE
)
INSERT INTO #DaysPerJob(GroupID, JobDesc, StartDate, EndDate) 
VALUES
        (23293, 'Food Prep', '2017-03-01', '2017-07-17')
    , (23293, 'Finisher', '2021-11-19', NULL)
    , (23293, 'Cashier', '2021-12-06', '2021-12-10')
    , (26208, '3rd SHift Stocker', '2019-09-25', '2020-11-05')
    , (26208, 'Order Fulfillment Assoc', '2020-08-05', '2021-04-16')
    , (26208, 'Customer Service Rep', '2021-05-10', '2021-10-15')
    , (26208, 'Delivery Driver', '2021-11-15', NULL)
    , (26208, 'Another Job', '2022-02-23', '2022-03-02')
    , (26208, 'Same Day Job Start as Prev Job End', '2022-03-01', NULL)

--SELECT * FROM #DaysPerJob dpj ORDER BY dpj.GroupID, dpj.StartDate, dpj.EndDate

/* Days Per Job Calculations - Attempts */
SELECT dj.GroupID, dj.JobDesc, dj.StartDate, dj.EndDate
    , LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.GroupID, dj.StartDate, dj.EndDate) AS PreviousJobEndDate
    , DATEDIFF(DAY, dj.StartDate, IsNull(dj.EndDate, GetDate())) AS daysPerJob
FROM #DaysPerJob dj
ORDER BY dj.GroupID, dj.StartDate, dj.EndDate

How do I obtain a SUM of the unique days employed per group?

The SQL Above will give you a table of Job Records. Each Job has a Start Date but not all jobs have an End Date which means they are still employed at that job.

The issue I have been struggling with is how to count the unique days employed. It is VERY easy to simply calculate the number of days per job using the DATEDIFF function however I am not currently able to account for other jobs within the same range as it would count those days twice.

I am ordering by the Start Date and then using LAG I compare the last jobs End Date to the next jobs Start Date. If the current jobs Start Date is <= the last jobs End Date we instead calculate the next jobs days using the last jobs End Date to the current Jobs End Date...

However the above condition had issues... what if my last job did not have an End Date or what if the last jobs End Date was also > the current Jobs End Date? This would mean that the entire current job falls within the same range as the last job and so we should NOT count ANY days and the day count would become 0 so that when the Total SUM of days is calculated it would not count the days in that job. It was THIS last issue that I could not figure out which has now lead me to posting this question here on Stack Overflow.

/* Some SQL below of some things I have tried */
/* Days Per Job Calculations - Attempts */
SELECT dj.GroupID, dj.JobDesc, dj.StartDate, dj.EndDate
    , LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.GroupID, dj.StartDate, dj.EndDate) AS PreviousJobEndDate
    
    /* Check if next record is within same date range.  The idea here is if the job is within the
     | same Range we replace the current Jobs Start Date with the last Jobs End Date
    */
    , CASE WHEN ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ) >= dj.StartDate 
        AND ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ) <= dj.EndDate

        THEN  IsNull( ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ), GetDate() )
        ELSE dj.StartDate

      END AS StartDateForSet
    /* The below CASE is the same logic as the above CASE but just an output stating if the
     | next job was found to be within the same range or if a NEW Set has begun.
    */
    , CASE WHEN ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ) >= dj.StartDate 
        AND ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ) <= dj.EndDate

        THEN 'InRange' 
        ELSE 'NewSet'

      END AS withinRangeCheck

    , DATEDIFF(DAY, dj.StartDate, IsNull(dj.EndDate, GetDate())) AS daysPerJob
    /* This is the field that I want to use to eventually SUM using GROUPing and aggregate functions however I first 
     | need to get it to correctly output the unique days.  If the current job falls within the previous jobs date
     | range the idea is that this calculation would account for that and move the End Date accordingly so it either
     |  does NOT count any days within the new job or counts the trailing days should the job end date fall after the previous job.
    */
    , DATEDIFF(DAY  /* StartDate */
        ,     (CASE WHEN( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ) >= dj.StartDate 
                AND ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ) <= dj.EndDate

                THEN IsNull( ( LAG(dj.EndDate) OVER (PARTITION BY dj.GroupID ORDER BY dj.StartDate, dj.EndDate) ), GetDate() )
                ELSE dj.StartDate

                END 
                ) 
            /* EndDate If Null Use Current Date */
            , IsNull(dj.EndDate, GetDate())

      ) AS DaysEmployedWithinSet

FROM #DaysPerJob dj
ORDER BY dj.GroupID, dj.StartDate, dj.EndDate

CodePudding user response：

Here is another answer derived after some time to wrangle the data. Please forgive me, I put this into a fromatting that was easier to work with. This should work.

/* Data Setup */
DROP TABLE IF EXISTS #DaysPerJob;
CREATE TABLE #DaysPerJob
(
    GroupID INT, JobDesc VARCHAR(100), StartDate DATE, EndDate DATE
)
INSERT INTO #DaysPerJob(GroupID, JobDesc, StartDate, EndDate) 
VALUES
        (23293, 'Food Prep', '2017-03-01', '2017-07-17')
    , (23293, 'Finisher', '2021-11-19', NULL)
    , (23293, 'Cashier', '2021-12-06', '2021-12-10')
    , (26208, '3rd SHift Stocker', '2019-09-25', '2020-11-05')
    , (26208, 'Order Fulfillment Assoc', '2020-08-05', '2021-04-16')
    , (26208, 'Customer Service Rep', '2021-05-10', '2021-10-15')
    , (26208, 'Delivery Driver', '2021-11-15', NULL)
    , (26208, 'Another Job', '2022-02-23', '2022-03-02')
    , (26208, 'Same Day Job Start as Prev Job End', '2022-03-01', NULL)

--SELECT * FROM #DaysPerJob dpj ORDER BY dpj.GroupID, dpj.StartDate, dpj.EndDate

/* Days Per Job Calculations - Attempts */

;WITH GapsMarked AS
(
    --Mark the start of an (null) value island within a group and rank the data for window functions below and/or joining back
    SELECT 
        GroupID, JobDesc,StartDate, EndDate,        
        Island = CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END,
        RowInGroup=ROW_NUMBER() OVER(PARTITION BY GroupID ORDER BY StartDate, EndDate)      
    FROM 
        #DaysPerJob
)
,VirtualGroups AS
(
    --Complete the IsIsland within group calculation started above
    SELECT 
        *,
        IsIsland = SUM(Island) OVER (PARTITION BY GroupID ORDER BY RowInGroup ROWS UNBOUNDED PRECEDING)     
    FROM 
        GapsMarked
)
,MinEndDateInIsland AS
(
    --This grabs the Min End Date to compare to the start date of each consecutive island record
    SELECT 
        V1.GroupID, V1.RowInGroup,              
        EndDateOrMinOverlapped=CASE WHEN MIN(V2.EndDate) >= V1.StartDate THEN   MIN(V2.EndDate)  ELSE V1.EndDate END        
    FROM 
        VirtualGroups V1
        LEFT OUTER JOIN VirtualGroups V2 ON V2.GroupID = V1.GroupID AND V2.RowInGroup <= V1.RowInGroup AND V2.IsIsland=0 
    GROUP BY
        V1.GroupID, V1.RowInGroup,V1.StartDate, V1.EndDate
)
--Final output
SELECT 
    G.GroupID, G.JobDesc, G.StartDate, G.EndDate,
    DayCalc=CASE WHEN G.IsIsland=0 THEN DATEDIFF(DAY, G.StartDate,N.EndDateOrMinOverlapped) ELSE NULL END
FROM
    MinEndDateInIsland N
    INNER JOIN VirtualGroups G ON G.GroupID = N.GroupID AND G.RowInGroup= N.RowInGroup
ORDER BY 
    G.GroupID, G.RowInGroup

CodePudding user response：

I didn't have access to a SqlServer instance to test this on, so this is SQLite syntax, but I don't think it should be hard to convert this.

The approach I took was to basically use a "Dates" table and then join the DaysPerJob table to it so you get records for each day a GroupId was active. Then you just rank based on the individual day and groupId to use to filter out "overlapped" days of jobs.

/* Just using a recursive CTE to create a DATE table */
/* If you have an existing date table, could use that instead */
WITH dates(date) AS (
  SELECT
    MIN(StartDate)
  FROM DaysPerJob
  UNION ALL
  SELECT
    DATE(date, ' 1 day')
  FROM dates
  WHERE date < date()
)
, ranked AS (
  SELECT
    d.date
  , j.StartDate
  , j.EndDate
  , j.GroupID
  , j.JobDesc
  , ROW_NUMBER() OVER (PARTITION BY d.date, j.GroupID) AS ranker
  FROM dates d
  LEFT JOIN DaysPerJob j
    ON date(j.StartDate) <= date(d.date)
  AND ifnull(j.EndDate, date()) >= date(d.date)
  WHERE j.GroupID IS NOT NULL
)
SELECT COUNT(*) AS days_worked, GroupID
FROM ranked r
WHERE r.ranker = 1
GROUP BY GroupID;