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

Question

/* 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

|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|

The Solution to this problem is Below based on the Chosen correct posted answer

|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|

I really thought there would be more answers to this question however this isn't an easy one... at least it wasn't for me nor was it something my coworkers were able to answer. Regardless there were two answers posted to this question. One post, however close it came, did not produce accurate counts of the days employed. I triple checked the data as well as checking the calculations in Excel and based on the dataset provided in this example the totals should look as they do below in the SQL Server version of using a Recursive CTE to create a dates table.

/* SUM Unique Days in Multiple Date Range Records (SQL Server).sql
 | SQL Server Example 
 | Desc: The below shows how to obtain the unique days employed.  Meaning we don't count the 
 |         same day twice should an individual be employed at more than job at any given time.
*/

/* 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, 'Starter', '2021-11-21', '2021-12-13')
    , (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)
;

/* Using a Recursive CTE to produce a dates table to later be JOINed on */
WITH Dates(date) AS
(
    SELECT MIN(StartDate) AS date
    FROM #DaysPerJob

    UNION ALL

    SELECT DATEADD(DAY, 1, date)
    FROM Dates
    WHERE date < GetDate()
)
, ranked AS
(   /* Needing to rank each job record in order to later remove the overlapping days when employed at more than one job at one time. */
    SELECT j.*, d.*
        , ROW_NUMBER() OVER (PARTITION BY j.GroupID, d.date ORDER BY j.GroupID, j.StartDate, IsNull(j.EndDate, GetDate())) AS ranker
    FROM Dates d
        LEFT JOIN #DaysPerJob j ON j.StartDate <= d.date
                                    AND IsNull(j.EndDate, GetDate()) >= d.date
    WHERE j.GroupID IS NOT NULL /* This filter removes all days in the Dates table where there was no employment */
        --AND j.GroupID = 26208  --23293

    --ORDER BY d.date, j.StartDate, IsNull(j.EndDate, GetDate()), j.GroupID
    --OPTION (MaxRecursion 0) 
)

    /* Non Aggregate Data - UnComment to view */
    /*
    SELECT * FROM ranked r WHERE r.GroupID IS NOT NULL
    ORDER BY r.date, r.StartDate, IsNull(r.EndDate, GetDate()), r.GroupID
    OPTION (MaxRecursion 0)
    */

/* Aggregated Data */
SELECT r.GroupID, COUNT(*) AS daysEmployed, MIN(date) AS minStartDate, MAX(date) AS maxEndDate
    , DATEDIFF(DAY, MIN(date), MAX(date)) AS TotalDaysInRange
    /* To get total number of days NOT employed we simply take the TotalDaysInRange and subtract the daysEmployed */
    , DATEDIFF(DAY, MIN(date), MAX(date)) - COUNT(*) AS unEmployedDays
FROM ranked r
WHERE r.ranker = 1
GROUP BY r.GroupID
ORDER BY r.GroupID
OPTION (MaxRecursion 0) /* The default MaxRecursion setting is 100. Generating more than 100 dates using this method will require the Option (MaxRecursion N) segment of the query, where N is the desired MaxRecursion setting. Setting this to 0 will remove the MaxRecursion limitation altogether */

Screenshot of totals grouped by GroupID:

Based on the screenshot as of today's date as of this posting 06.02.22 the totals are:

GroupID 23293 : 335 Days Employed

GroupID 26208 : 929 Days Employed

This SO Post has excellent examples of how to populate a dates table and some of the answers accomplish this feat without needing to use Option (MaxRecursion)

Get a list of dates between two dates using a function

score 1 · Accepted Answer · answered Jun 01 '22 at 22:35

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;

This answer to this problem is very intuitive and eliminates the need to actually apply any date functions. Once the date table is produced you can simply count the unique days after removing the duplicate records using a ranker. It's very simple. — Code Novice, Jun 02 '22 at 18:56

score 0 · Answer 2 · answered Jun 01 '22 at 18:42

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

I certainly see the effort that went into this and the approach you took was the approach I was also leading towards. However, after seeing the other answer to this problem is very intuitive and eliminates the need to actually apply any date functions. Once the date table is produced you can simply count the unique days after removing the duplicate records using a ranker. I would have loved to given your answer an UpVote however the totals are incorrect or at least I was not able to find a way to get them to be accurate. — Code Novice, Jun 02 '22 at 18:56
Thanks for the comment. I agree, using a date table is a more elegant solution and requires less dips into the data. I got lost in attempting to solve this unique problem as it was stated. I am glad you found a solution. — Ross Bush, Jun 02 '22 at 19:25
http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function — Code Novice, Jun 17 '22 at 21:17

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

2 Answers2

Linked