sum the days of the event without saturdays and sundays

Question

I need a query that counts all days of the type "Vacation" of a given IT specialist from the current year, not including weekends, the vacation may last several days or one day. If the leave lasts for several days, the beginning of the leave is the start, and the end of the leave is the end, if the leave is one day, the start and end have the same date

At the moment, I have added up the leave, it verifies whether the leave lasts one day or several days

I need to exclude weekends from the sum of vacation days

Table: kal_termin

id,start,end,id_typ

Table: kal_uczestnik

id,id_termin,id_informatyk

My query

SELECT  kal_uczestnik.id_informatyk, SUM(
    CASE 
        WHEN start = end THEN 1
        ELSE DATEDIFF(end, start) + 1
    END
) as days_off 
FROM kal_termin,kal_uczestnik
WHERE kal_termin.id=kal_uczestnik.id_termin AND kal_uczestnik.id_informatyk = 119 AND kal_termin.id_typ = 1 AND YEAR(start) = YEAR(CURRENT_DATE) 
GROUP BY  kal_uczestnik.id_informatyk;

Does this answer your question? [MySQL function to find the number of working days between two dates](https://stackoverflow.com/questions/1828948/mysql-function-to-find-the-number-of-working-days-between-two-dates) — shanmukha vangaru, Feb 12 '23 at 13:37

user1191247 · Accepted Answer · 2023-02-12T21:08:47.643

You need to use a calendar table or a recursive Common Table Expression (CTE), to build a calendar, as in this example:

WITH RECURSIVE `calendar` (`date`, `is_weekend`) AS (
    SELECT
        MAKEDATE(YEAR(CURRENT_DATE), 1), -- first day of current year
        DAYOFWEEK(MAKEDATE(YEAR(CURRENT_DATE), 1)) IN (1, 7) -- is day Sat or Sun
    UNION ALL
    SELECT
        `date` + INTERVAL 1 DAY,
        DAYOFWEEK(`date` + INTERVAL 1 DAY) IN (1, 7)
    FROM `calendar`
    WHERE `date` + INTERVAL 1 DAY < MAKEDATE(YEAR(CURRENT_DATE) + 1, 1) -- first day of next year
)
SELECT  `u`.`id_informatyk`, COUNT(`c`.`date`) AS `days_off`
FROM `kal_termin` `t`
JOIN `kal_uczestnik` `u`
    ON `t`.`id` = `u`.`id_termin`
    AND `u`.`id_informatyk` = 119
    AND `t`.`id_typ` = 1
JOIN `calendar` `c`
    ON `c`.`date` BETWEEN `t`.`start` AND `t`.`end`
    AND `c`.`is_weekend` = 0
GROUP BY  u.id_informatyk;

The calendar CTE builds a list of all days in the current year, along with whether each day is_weekend. We then join that to your existing query based on whether the calendar.date is between start and end, only including the days where is_weekend = 0. This assumes that start and end are both included in the days off. You can change the join criteria if that is not the case.

Thanks to @shanmukhavangaru for flagging this question. I was unaware of this approach. Unfortunately, the accepted answer has the wrong matrix for both this question, and the one referenced, as it does not include the end date. There is an answer way down the page which provides the correct string.

If you want to use the suggested solution the correct matrix, when including the start and end dates is:

 | M T W T F S S
-|--------------
M| 1 2 3 4 5 5 5
T| 5 1 2 3 4 4 4
W| 4 5 1 2 3 3 3
T| 3 4 5 1 2 2 2
F| 2 3 4 5 1 1 1
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

And, the concatenated string is:

1234555512344445123333451222234511112345001234550

So given your existing query, the solution would be:

SELECT
    u.id_informatyk,
    SUM(
        5 * (DATEDIFF(t.end, t.start) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(t.start) + WEEKDAY(t.end) + 1, 1)
    ) as days_off 
FROM kal_termin t
JOIN kal_uczestnik u
    ON t.id = u.id_termin AND u.id_informatyk = 119
WHERE t.id_typ = 1
AND t.start >= MAKEDATE(YEAR(CURRENT_DATE), 1) -- start of current year
AND t.start < MAKEDATE(YEAR(CURRENT_DATE) + 1, 1) -- start of next year
GROUP BY  u.id_informatyk;

There is a problem with the above queries. With input data start: 2023-03-02 00:00:00 end: 2023-03-07 23:59:00 It calculates 4 days instead of 3 days — fejmintv, Feb 13 '23 at 09:26
No, that is correct - Thu 2nd Mar, Fri 3rd Mar, Mon 6th Mar & Tue 7th Mar. They both function on dates, as per your query attempt. If you want to do it based on datetime values, you will need to provide detailed sample data and rules for calculating "days". — user1191247, Feb 13 '23 at 09:52

sum the days of the event without saturdays and sundays

1 Answers1