0

So, I have a table that holds records of employees performing certain tasks. A task has an id, a taskname, an employee and a startDate and endDate.

Using the following query that was provided to me by @MatBailie here (thanks!), I am able to grab how many days every employee has spent on every task in the current year:

SELECT
taskname,
employee,
startDate,
endDate,
SUM(DATEDIFF(startDate, endDate)+1) AS total_days,
FROM
schedule
WHERE
startDate<='2023-12-31'
AND
endDate>='2023-01-01'
GROUP BY
employee,
taskname

However, sometimes a task overlaps two years. For example, when a task has a startDate of the 22nd of December, and an endDate of the 10th of January, the total duration of this task is 10 days in the current year, and 10 days in the next. This is where the problem arises, because it counts all 20 days as if they were in this year because the event complies with the startDate and endDate requirements (the 'WHERE' clauses) and then the entire duration is added to the SUM.

So my question is: how can I modify my query so that it only counts the amount of days (in the SUM(DATEDIFF)) that fall within a specified timerange (i.e. the current year or quarter).

Thanks in advance for your help!

Laurens Swart
  • 1,234
  • 9
  • 24

1 Answers1

2
SELECT
  taskname,
  employee,
  SUM(
    DATEDIFF(
      LEAST(     enddate, '2023-12-31'),
      GREATEST(startdate, '2023-01-01')
    )
    +1
  ) AS total_days,
FROM
  schedule
WHERE
  startDate <= '2023-12-31'
  AND
  endDate   >= '2023-01-01'
GROUP BY
  employee,
  taskname
MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Ah! This makes sense! If endDate is larger than 2023-12-31, it counts the days from start till december 31st, and if startDate is smaller than 2023-01-01, it counts the days from January 1st till end. You're amazing! – Laurens Swart Jan 10 '23 at 08:53
  • Another follow-up question :'), but this one is *really* hard (I think): https://stackoverflow.com/questions/75067940/only-count-working-days-in-a-datediff-mysql – Laurens Swart Jan 10 '23 at 09:39