Only count working days in a DATEDIFF (MySQL)-CodePudding

So, next problem :'), I have the following query that @MatBailie provided to me here (thanks again!):

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

This query will tell me how many days a certain employee has spent on a certain task in a given period of time, and it works great!

The next thing I would like to do however, is to substract non-working days from the SUM of DATEDIFFs for some of the tasks (e.g. when the task has "count_non_working_days= 0" in a reference table called 'activities').

For example, my schedule also keeps track of the amount of days off every employee has taken (days off are also scheduled as tasks). But of course, days off that fall in a weekend or on a holiday should not be counted towards the total of days off a person has taken in a year. (Note that I did consider scheduling days off only on weekdays/non-holidays, but this is not a practical option in the scheduling software I use because employees request a leave from date A to date B, and this request is approved or denied as-is (they don't make 3 holiday requests excluding the weekends if they want to go on a vacation for 3 weeks, if you get my drift).

So, if an employee goes on a vacation for 10 days, this is counted as 10 days off, but this holiday may have 1 or 2 weekends in it, so the sum of days of that the employee has taken off should be 6, 7 or 8, and not 10. Furthermore, if it has a holiday such as Easter Monday in it (I have all dates of my holidays in a PHP array), this should also be subtracted.

I have tried the solutions mentioned here, but I couldn't get them to work (a) because those are in SQL server and (b) because they don't allow putting in an array of holidays, (c) nor allow toggling the subtraction on and off depending on the event type.

Here's my attempt of explaining what I'm trying to do in my pseudo-SQL:

SELECT
  taskname,
  employee,
  IF( activities.count_non_working_days=1,  
    -- Just count the days that fall in the current year: 
    SUM(
      DATEDIFF(
        LEAST(      enddate, '2023-12-31'),
        GREATEST( startdate, '2023-01-01')
      ) 
        1
    ) AS total_days,
    -- Subtract the amount of saturdays, sundays and holidays:
    SUM(
      DATEDIFF(
        LEAST(      enddate, '2023-12-31'),
        GREATEST( startdate, '2023-01-01')
      ) 
      - [some way of getting the amount of saturdays, sundays and holidays that fall within this date range]
        1
    ) AS total_days
  )
FROM
  schedule
LEFT JOIN
  activities
  ON activity.name = schedule.name
WHERE
  startDate <= '2023-12-31'
  AND
  endDate   >= '2023-01-01'
GROUP BY
  employee,
  taskname

I know the query above is probably faulty on so many levels, but I hope it clarifies what I'm trying to do.

Thanks once more for all the help!

Edit: basically I need something like this, but in MySQL and preferably with a toggle that turns the subtraction on or off depending on the task type.

Edit 2: To clarify: my schedule table holds ALL activities, including holidays. For example, some records may include:

employee	taskname	startDate	endDate
Mr. Anderson	Programming	2023-01-02	2023-01-06
Mr. Anderson	Programming	2023-01-09	2023-01-14
Mr. Anderson	Vacation	2023-01-14	2023-01-31

In another table, Programming is defined as "count_non_working_days=1", because working in the weekends should count, while Vacation is defined as "count_non_working_days=0", because taking a day off on the weekend should not count towards your total amount of days taken off.

The totals for this month should therefore state that:

Mr. Anderson has done Programming for 11 days (of which 1 was on a saturday)

Mr. Anderson has taken 12 days off for (because the 2 weekends in this period don't count as days off).

CodePudding user response：

Create a calendar table, with every date of interest (so, something like 2000-01-01 to 2099-01-01) and include columns such as is_working_day which can be set to TRUE/FLASE or 1/0. Then you can update that column as necessary, and join on that table in your query to get working dates that the employee has booked off.

In short, you count the relevant dates, rather than deducting the irrelevant dates.

SELECT
  s.employee,
  s.taskname,
  COUNT(*)    AS total_days,
FROM
(
    schedule    AS s
  INNER JOIN
    activities  AS a
      ON  a.taskname = s.taskname
)
INNER JOIN
  calendar    AS c
    ON  c.calendar_date  >= s.startDate
    AND c.calendar_date  <= s.endDate
    AND c.is_working_day >= 1 - a.count_non_working_days 
WHERE
      c.calendar_date >= '2023-01-01'
  AND c.calendar_date <= '2023-12-31'
GROUP BY
  s.employee,
  s.taskname

Your calendar table can then also include flags such as is_weekend, is_bank_holiday, is_fubar, is_amazing, etc, and the is_working_day can be a computed column from those inputs.

Note on is_working_day filter...

   WHERE
     ( count_non_working_day = 1 AND is_working_day IN (0, 1) )
     OR
     ( count_non_working_day = 0 AND is_working_day IN (   1) )


   -- change to (1 - count_non_working_day)

   WHERE
     ( (1 - count_non_working_day) = 0 AND is_working_day IN (0, 1) )
     OR
     ( (1 - count_non_working_day) = 1 AND is_working_day IN (   1) )


   -- simplify

   WHERE
     ( (1 - count_non_working_day) <= is_working_day )
     OR
     ( (1 - count_non_working_day) <= is_working_day )
  

   -- simplify

   WHERE
     ( (1 - count_non_working_day) <= is_working_day )

Demo: https://dbfiddle.uk/YAmpLmVE

CodePudding user response：

This is to calculate all the weeekends between two giving dates It may help you :

SELECT (
    ((WEEK('2022-12-31') - WEEK('2022-01-01')) * 2) -
    (case when weekday('2022-12-31') = 6 then 1 else 0 end) -
    (case when weekday('2022-01-01') = 5 then 1 else 0 end)
)

You will have to substract also holidays that fall within this date range.