count saturdays before 15 date as working days and skip others

Question

My company policy is that we count the Saturdays before 15 dates as working days and after 15 days as company holiday kindly tell me the function to count working days of the month skip all Sundays and Saturdays before 15 date in Query

Answer 1

This works without calendar table and by month. And you only need to change the date value @curdt variable. It can be any dates within the month as long as its not less or more than the real calendar dates e.g '2019-07-00' or '2019-07-32' - this won't work.

#1 : Setting variable.
SET @curdt := '2019-07-01'; #only need to change the date here.
SET @startdate := LAST_DAY(@curdt-INTERVAL 1 MONTH)+INTERVAL 1 DAY;
SET @lastdate := LAST_DAY(@curdt);

#2 : Count workdays. 

SELECT  SUM(CASE WHEN DAY(dt) <= 15 AND WEEKDAY(dt)=6 THEN 0
            WHEN DAY(dt) > 15 AND WEEKDAY(dt) IN (5,6) THEN 0
            ELSE 1 END) AS Workdays 
FROM
(SELECT dt 
 FROM   (
         #custom calendar
         SELECT CONCAT_WS('-',curmy,CONCAT(n2,n1)) dt 
         FROM
                (SELECT 0 AS n1 UNION 
                 SELECT 1 UNION
                 SELECT 2 UNION
                 SELECT 3 UNION
                 SELECT 4 UNION
                 SELECT 5 UNION
                 SELECT 6 UNION
                 SELECT 7 UNION
                 SELECT 8 UNION
                 SELECT 9 ) a CROSS JOIN
                (SELECT 0 n2 UNION 
                 SELECT 1 UNION 
                 SELECT 2 UNION 
                 SELECT 3) b CROSS JOIN
                (SELECT LEFT(@curdt,7) curmy) c
          ) zz
 WHERE  dt BETWEEN @startdate AND @lastdate) XX
 GROUP BY MONTH(dt);

Just tried a new query and might just add in here for future reference:

SET @curdate := CURDATE()-INTERVAL 1 MONTH;

SELECT * FROM
(SELECT CONCAT_WS('-',df,CONCAT(b,c)) dm FROM 
(SELECT DATE_FORMAT(@curdate, '%Y-%m') df) a CROSS JOIN
(SELECT 0 b UNION SELECT 1 b UNION SELECT 2 UNION SELECT 3) b CROSS JOIN
(SELECT 0 c UNION
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5 UNION
SELECT 6 UNION
SELECT 7 UNION
SELECT 8 UNION
SELECT 9) c) d WHERE DAY(dm) BETWEEN 1 AND DAY(LAST_DAY(dm))
ORDER BY dm;

This method is for MariaDB - using it's sequence engine: From MariaDB 10.1, the Sequence engine is installed by default.

SELECT DATE_FORMAT(CONCAT_WS('-',a.seq,b.seq,c.seq), '%Y-%m-%d') dates
   FROM seq_2020_to_2021 a -- year range
   CROSS JOIN seq_1_to_12 b -- month range
   CROSS JOIN seq_1_to_31 c  -- day range
HAVING dates IS NOT NULL; -- excluding invalid dates returned as NULL 

Answer 2

You can try either one method below or can use the query which was shared by @Ankit.

If you have a table like calendar it will be useful in many ways.

METHOD: 1

If you have a Calendar table you can run the below query,

SELECT 
    SUM(CASE WHEN WEEKDAY(cal.cal_date) <> 6 AND DAY(cal.cal_date) < 16 THEN 1
             WHEN WEEKDAY(cal.cal_date) NOT IN (5, 6) AND DAY(cal.cal_date) > 15 THEN 1 END) AS 'working days'
FROM 
    calendar cal
WHERE 
    cal.cal_date BETWEEN '2019-10-01' AND '2019-10-31';

METHOD: 2

If creating a TABLE OR VIEW is restricted can generate a range of dates using the below query

SELECT 
    SUM(CASE WHEN WEEKDAY(cal.cal_date) <> 6 AND DAY(cal.cal_date) < 16 THEN 1
             WHEN WEEKDAY(cal.cal_date) NOT IN (5, 6) AND DAY(cal.cal_date) > 15 THEN 1 END) AS 'working days'
FROM 
    (SELECT 
        ('1970-01-01' + INTERVAL (((((`t4`.`t4` * 10000) + (`t3`.`t3` * 1000)) + (`t2`.`t2` * 100)) + (`t1`.`t1` * 10)) + `t0`.`t0`) DAY) AS `cal_date`
    FROM
        ((((((SELECT 0 AS `t0`) UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `t0`
        JOIN (SELECT 0 AS `t1` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `t1`)
        JOIN (SELECT 0 AS `t2` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `t2`)
        JOIN (SELECT 0 AS `t3` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `t3`)
        JOIN (SELECT 0 AS `t4` UNION ALL SELECT 1 AS `1` UNION ALL SELECT 2 AS `2` UNION ALL SELECT 3 AS `3` UNION ALL SELECT 4 AS `4` UNION ALL SELECT 5 AS `5` UNION ALL SELECT 6 AS `6` UNION ALL SELECT 7 AS `7` UNION ALL SELECT 8 AS `8` UNION ALL SELECT 9 AS `9`) `t4`)) AS cal
WHERE 
    cal.cal_date BETWEEN '2019-10-01' AND '2019-10-31';

Fiddle here

Answer 3

You can use below code to count the working days as per your requirement -

CREATE FUNCTION FN_CNT_Working_days(StartDate DATE,
                                    EndDate   DATE) 
RETURNS INT
BEGIN
     DECLARE WORKING_DAYS INT;
     SELECT
   (DATEDIFF(EndDate, DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 16))) + 1)
  -(FLOOR(DATEDIFF(EndDate, DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 16)))/7) * 2)
  -(CASE WHEN DAYNAME(DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 16))) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DAYNAME(EndDate) = 'Saturday' THEN 1 ELSE 0 END) 
   +
   (DATEDIFF(DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 15)), StartDate) + 1)
  -(FLOOR(DATEDIFF(DATE(CONCAT(YEAR(EndDate), '-', MONTH(EndDate), '-', 15)), StartDate)/7))
  -(CASE WHEN DAYNAME(StartDate) = 'Sunday' THEN 1 ELSE 0 END)
     INTO WORKING_DAYS;

     RETURN (WORKING_DAYS);
END;

Here is the fiddle. Also you need to take care of holidays along with this code.