CODEWITHSUNDEEP
mysql
fejmintv
fejmintv

Reputation: 31

sum the days of the event without saturdays and sundays

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;

Upvotes: 0

Views: 62

Answers (1)

user1191247
user1191247

Reputation: 12998

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;

Upvotes: 1

Related Questions