CODEWITHSUNDEEP
mysqlmysql-8.0
Bisoux
Bisoux

Reputation: 522

Get range/count of days based off a single date field

I have requirement where i will need to get the number of days a role an employee was on.

Scenario 1

   EmployeeId    role   effectiveFrom
      1           A      1-Jan-2021
      1           B      15-Jan-2021

No further roles are available for the month of Jan for role A therefore the number of days for role A would be 14.

Scenario 2

   EmployeeId    role   effectiveFrom
      1           A      1-Jan-2021

No further roles are available for the month of Jan therefore the number of days for role A would be 31 i.e the entire month of January. For the month of February i would expect to get 28 as the role would be effective for the entire month of february as well.

Scenario 3

   EmployeeId    role   effectiveFrom
      1           A      1-Jan-2021
      1           B      15-Jan-2021
      1           A      25-Jan-2021

To get the number of days for role A the logic would be

The query i have come up with so far is this,

SELECT 
    DATEDIFF(MAX(effectiveFrom),
            IF(MIN(effectiveFrom) = MAX(effectiveFrom),
                MIN(effectiveFrom),
                MIN(effectiveFrom))) + 1 daysWorked
FROM
    EmployeeRoles
WHERE grade = 'A'  
GROUP BY `employeeId`,effectiveFrom;

which would only give the result as 1 day for Scenario 1. Could someone guide me on the practical way of handling the scenarios. I have looked at loops, window functions but i am at a loss on the best way to proceed.

dbfiddle

Upvotes: 1

Views: 82

Answers (1)

Luuk
Luuk

Reputation: 14939

When scenario2 has 31 days from 1-jan, until the end of the month, I would suspect that from 25-jan, until the end of the month, is 7 days, and not 6, as you write in scenario3.

The number of days, using above calculation:

SELECT
   employeeID,
   grade,
   effectiveFrom,
   DATEDIFF(COALESCE(LEAD(effectiveFrom) 
                             OVER (PARTITION BY employeeID, grade ORDER By effectiveFrom),
                     DATE_ADD(LAST_DAY(effectiveFrom),INTERVAL 1 DAY)),
            effectiveFrom) as '#Days'
FROM EmployeeRole;

This can be grouped, and summed giving:

SELECT
   employeeID,
   grade,
   SUM(`#Days`)
FROM (

SELECT
   employeeID,
   grade,
   effectiveFrom,
   DATEDIFF(COALESCE(LEAD(effectiveFrom) 
                            OVER (PARTITION BY employeeID, grade ORDER By effectiveFrom),
                     DATE_ADD(LAST_DAY(effectiveFrom),INTERVAL 1 DAY)),
            effectiveFrom) as '#Days'
FROM EmployeeRole
) x
GROUP BY
  employeeID,
   grade;

output:

employeeID grade SUM(#Days)
1 A 14
1 B 17
2 A 31
3 A 21
3 B 10

see: DBFIDDLE

EDIT: The results were incorrect because the next effectiveFrom date was determined using OVER (PARTITION BY employeeID ORDER By effectiveFrom). this is not correct, because the grade should be taken into account too.

I corrected it to OVER (PARTITION BY employeeID, grade ORDER By effectiveFrom)

P.S. I also corrected this in the piece above the EDIT!

see: DBFIDDLE

Upvotes: 2

Related Questions