Sum amount of days an employee performs a task using DATEDIFF, but only count days that fall within-CodePudding

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!

CodePudding user response：

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