How to count weekday, Saturday & Sunday only between two dates in MySQL

Ajit_Kr
Ajit_Kr used Ask the Experts™
on
I need to write a proc or function for calculating total number of weekdays, saturday & sunday between start & end date (including the both dates). And due to performance issue I don't want to apply date-wise loop in the logic.

Please suggest the proper logic to implement it in MySQL.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:


SELECT SUM(IF(weekday( your_date_col) IN (0,1,2,3,4), 1,0)) weedays,
           SUM(IF(weekday( your_date_col) IN (5,6), 1,0)) weekenddays
FROM your_table
WHERE date_col BETWEEN '2009-01-01' and '2009-09-20';

Author

Commented:
Hi racek,
I think you are considering date as a column in the table. But I will have Start_Date and End_Date as input, I have to calculate no. of weekdays, Saturday & Sunday based on these two dates without interacting any table. Please consider this.

Commented:
USE  algorithm:

SET i = select to_days('2009-01-01');
SET week_end = 0;
SET week_day = 0;

WHILE i < select to_days('2009-09-01') DO
IF WEEKDAY(FROM_DAYS(i) IN (5,6)
    THEN week_end = week_end +1
    ELSE week_day =  week_day + 1
END IF;
SET i = i + 1;
END;
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
I want to avoid looping as there would be performance issue. Is it posible without it?

Commented:
Yes, assuming you have a Calendar table, which is extremely useful for this sort of thing.

SELECT
 COUNT(CASE WHEN DayOfWeek IN ('Saturday','Sunday') THEN 1 END) we,
 COUNT(*) cnt
FROM Calendar
WHERE CalDate BETWEEN  Start_Date AND End_Date ;

Author

Commented:
what would be in Calendar table?

Commented:
SELECT weekday( first_day) -  weekday(last_day);
if diif = 1 then all week day has same number as weeks no of week between two dates ...

SELECT DATE_DIFF(first_day,last_day)/7 ...

then you need to add or sub 1 to days missing the last week ...


Commented:
in calender table - one column with date for all days

Author

Commented:
No I can't use the calendar table. Input would be only two date variable i.e. start_date & end_date. Just suggest with this condition only, pls don't use any table.

Author

Commented:
RE: ID: 24893574
It wouldn't work in all scenarios.

Commented:
do yopu understan this??????
SELECT FLOOR(DATE_DIFF('2009-01-01','2009-02-28')/7) as whole_weeks; -- whole weeks
SELECT MOD(DATE_DIFF('2009-01-01','2009-02-28')/7) as days_over; -- last part of week
SELECT WEEKDAY('2009-01-01') as first_day; -- returns 0 - 6 as weekday
IF first_day + days_over >= 0 
  OR (first_day + days_over > 6 AND first_day + days_over - 7 > 0) 
  THEN monday = whole_weeks + 1 
  ELSE monday = whole_weeks 
ELSE IF
first_day + days_over >= 1 
  OR (first_day + days_over > 6 AND first_day + days_over - 7 > 1) 
  THEN tuesday = whole_weeks + 1 
  ELSE tuesday = whole_weeks 
ELSE IF
first_day + days_over >= 2 
  OR (first_day + days_over > 6 AND first_day + days_over - 7 > 2) 
  THEN wednesday = whole_weeks + 1 
  ELSE wednesday = whole_weeks 
 
etc

Open in new window

Commented:
small fix in first 3 selects
SELECT FLOOR(DATEDIFF('2009-02-28','2009-01-01')/7) as whole_weeks; -- whole weeks
SELECT MOD(DATEDIFF('2009-02-28','2009-01-01'),7) as days_over; -- last part of week
SELECT WEEKDAY('2009-01-01') as first_day; -- returns 0 - 6 as weekday

Open in new window

Commented:
CASE is not correct yeat, but if you will play with it you can get it.

When it works, you can CREATE a STORED PROCEDURE and replace dates with parameters ...
SELECT FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) as whole_weeks; -- whole weeks
SELECT MOD(DATEDIFF('2009-07-20','2009-01-01'),7) as days_over; -- last part of week
SELECT WEEKDAY('2009-01-01') as first_day; -- returns 0 - 6 as weekday
SELECT WEEKDAY('2009-07-20');
 
SELECT CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 0))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as monday ,
CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 1))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as tuesday ,
CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 2))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as we ,
CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 3))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as th ,
CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 4))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as fr ,
CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 5))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as sa ,
CASE WHEN
  (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) <  7)
   OR (WEEKDAY('2009-01-01') + MOD(DATEDIFF('2009-07-20','2009-01-01'),7) > 6
  AND (WEEKDAY('2009-01-01')  + MOD(DATEDIFF('2009-07-20','2009-01-01'),7)- 7 > 6))
  THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
  ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
  END as su ;

Open in new window

Author

Commented:
I have tried for Start_Date = '2009-06-29' and End_Date = '2009-07-31', during this date range there are 4 Saturday & 4 Sunday but this query showing it 5.
Still there are few scenarios where it is not working.

Commented:

SELECT FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) as whole_weeks; -- whole weeks
SELECT WEEKDAY('2009-01-01') as first_day; -- returns 0 - 6 as weekday
SELECT WEEKDAY('2009-07-20') as last_day;
 
 
SELECT CASE WHEN (0  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (0 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as mo,
CASE WHEN (1  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (1 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as tu,
CASE WHEN (2  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (2 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as we,
CASE WHEN (3  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (3 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as th,
CASE WHEN (4  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (4 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as fr,
CASE WHEN (5  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (5 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as sa,
CASE WHEN (6  BETWEEN WEEKDAY('2009-01-01') AND WEEKDAY('2009-07-20')
             AND WEEKDAY('2009-07-20') > WEEKDAY('2009-01-01') )
              OR (6 BETWEEN WEEKDAY('2009-07-20') AND WEEKDAY('2009-01-01')
             AND WEEKDAY('2009-07-20') < WEEKDAY('2009-01-01') )
THEN FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7) + 1
ELSE FLOOR(DATEDIFF('2009-07-20','2009-01-01')/7)
END as su
;

Open in new window

Commented:

DELIMITER $$
DROP FUNCTION IF EXISTS `xxx`.`get_weekday` $$
CREATE FUNCTION `xxx`.`get_weekday` (first_date date, last_date date, curr_week_day int) RETURNS INT
BEGIN
DECLARE days_tot int;
DECLARE whole_weeks int;
DECLARE first_day int;
DECLARE last_day int;
SET whole_weeks = FLOOR(DATEDIFF(last_date,first_date)/7) ;
SET first_day = WEEKDAY(first_date) ;
SET last_day = WEEKDAY(last_date)  ;
IF curr_week_day  BETWEEN first_day AND  last_day
           AND  last_day > first_day
           OR ( curr_week_day BETWEEN last_day AND first_day
           AND  last_day <  first_day  )
THEN SET days_tot = whole_weeks + 1;
ELSE SET days_tot = whole_weeks ;
END IF;
RETURN  days_tot;
END $$
DELIMITER ;
 
 
 
 
SELECT
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 0) as mo,
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 1) as tu,
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 2) as we,
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 3) as th,
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 4) as fr,
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 5) as sa,
  `xxx`.`get_weekday` ('2009-01-01', '2009-07-20', 6) as su;

Open in new window

Author

Commented:
I have check your logic with following dates and the result seems wrong for Monday, Saturday & Sunday in both logic mentioned above in last two comment.

SELECT
  `get_weekday` ('2011-12-16', '2011-12-27', 0) as mo,
  `get_weekday` ('2011-12-16', '2011-12-27', 1) as tu,
  `get_weekday` ('2011-12-16', '2011-12-27', 2) as we,
  `get_weekday` ('2011-12-16', '2011-12-27', 3) as th,
  `get_weekday` ('2011-12-16', '2011-12-27', 4) as fr,
  `get_weekday` ('2011-12-16', '2011-12-27', 5) as sa,
  `get_weekday` ('2011-12-16', '2011-12-27', 6) as su;

The logic should satisfy all scenarios. It is were I stuck.

Commented:
show me your result pls

Author

Commented:
Result:

mo      tu      we      th      fr      sa      su
1      2      2      2      2      1      1

Author

Commented:
Result:

mo      tu      we      th      fr      sa      su
1         2       2         2        2       1       1

Commented:

....
2, 2, 1, 1, 2, 2, 2
DELIMITER $$
DROP FUNCTION IF EXISTS `xxx`.`get_weekday` $$
CREATE FUNCTION `xxx`.`get_weekday` (first_date date, last_date date, curr_week_day int) RETURNS INT
BEGIN
DECLARE days_tot int;
DECLARE whole_weeks int;
DECLARE first_day int;
DECLARE last_day int;
 
SET whole_weeks = FLOOR((DATEDIFF(last_date,first_date)+1)/7) ;
SET first_day = WEEKDAY(first_date) ;
SET last_day = WEEKDAY(last_date)  ;
 
IF first_day - last_day = 1 OR first_day - last_day = -6 THEN
   SET days_tot = whole_weeks;
ELSEIF curr_week_day  >= first_day AND  curr_week_day  <= last_day THEN
   SET days_tot = whole_weeks + 1;
ELSEIF (curr_week_day >= first_day OR curr_week_day <= last_day) and first_day > last_day THEN
   SET days_tot = whole_weeks + 1;
ELSE
   SET days_tot = whole_weeks ;
END IF;
RETURN  days_tot;
END $$
DELIMITER ;

Open in new window

Author

Commented:
please check the query, it is giving me old result.

Commented:
reneme the stored procedure and execute the new one - it works for me...

Commented:
rename

Author

Commented:
ok, changed query works fine for '2011-12-16' to '2011-12-27, but it fails for '2011-06-29' to '2011-07-31'

SELECT
  `get_weekday` ('2011-06-29', '2011-07-31', 0) as mo,
  `get_weekday` ('2011-06-29', '2011-07-31', 1) as tu,
  `get_weekday` ('2011-06-29', '2011-07-31', 2) as we,
  `get_weekday` ('2011-06-29', '2011-07-31', 3) as th,
  `get_weekday` ('2011-06-29', '2011-07-31', 4) as fr,
  `get_weekday` ('2011-06-29', '2011-07-31', 5) as sa,
  `get_weekday` ('2011-06-29', '2011-07-31', 6) as su;

Output:
mo      tu      we      th      fr      sa      su
4      4      5      5      5      5      5

Output should be:
mo      tu      we      th      fr      sa      su
5      5      5      5      5      4      4
Commented:
You are wrong :-( because you mix 2009 and 2011 :-)
this: 5      5      5      5      5      4      4 is correct for 2011
tis 4      4      5      5      5      5      5 for 2009

DELIMITER $$
DROP FUNCTION IF EXISTS `xxx`.`get_weekday` $$
CREATE FUNCTION `xxx`.`get_weekday` (first_date date, last_date date, curr_week_day int) RETURNS INT
BEGIN
DECLARE days_tot int;
DECLARE whole_weeks int;
DECLARE first_day int;
DECLARE last_day int;
 
SET whole_weeks = FLOOR((DATEDIFF(last_date,first_date)+1)/7) ;
SET first_day = WEEKDAY(first_date) ;
SET last_day = WEEKDAY(last_date)  ;
 
IF first_day - last_day = 1 OR first_day - last_day = -6 THEN
   SET days_tot = whole_weeks;
ELSEIF curr_week_day  >= first_day AND  curr_week_day  <= last_day and first_day <= last_day THEN
   SET days_tot = whole_weeks + 1;
ELSEIF (curr_week_day >= first_day OR curr_week_day <= last_day) and first_day > last_day THEN
   SET days_tot = whole_weeks + 1;
ELSE
   SET days_tot = whole_weeks ;
END IF;
RETURN  days_tot;
END $$
DELIMITER ;

Open in new window

Commented:
(and improved function)

Author

Commented:
gr8 job done, thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial