# Getting the number of Saturdays and Sundays in a month

I am trying to get the number of Saturdays and Sundays for a given month represented by an end_date and a start date.

I have been trying to understand the algorithm in the SO question here . I have understood the matrix they have created but I don't get the algorithm that is being used, plus the thing is just the opposite of what is needed, and this thing is pretty new to me, mostly I do normal `select`, `insert`, `update` and `joins` in mysql.

It would be helpful to get some ideas on this issue.

I'll suggest the calendar table also discussed in that thread. It will help for a lot of calculations beside this one. Your query can then be expressed as:

``````select count( case when DAYOFWEEK(calendar_date) in (1,7) then 1 end)
from Calendar
where calendar_date between :start_date and :stop_date
``````

or as:

``````select count(1)
from Calendar
where calendar_date between :start_date and :stop_date
and DAYOFWEEK(calendar_date) in (1,7)
``````

If you want the number of Saturdays and Sundays in a given month, you can do this

First, generate a calender using a given date

``````SET @GivenDate = DATE(NOW());
SELECT dt FROM
(SELECT DISTINCT (MAKEDATE(YEAR(@GivenDate),1) +
INTERVAL (MONTH(@GivenDate)-1) MONTH + INTERVAL (x*y+z-1) DAY) dt FROM
(SELECT 1 x UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8) A,
(SELECT 1 y UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) B,
(SELECT 0 z UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) C) cal
WHERE MONTH(@GivenDate) = MONTH(dt);
``````

Put the calendar in a subquery and do an aggregation of the days of the week

``````SET @GivenDate = DATE(NOW());
SELECT COUNT(1) WeekendDays FROM
(SELECT dt FROM (SELECT DISTINCT (MAKEDATE(YEAR(@GivenDate),1) +
INTERVAL (MONTH(@GivenDate)-1) MONTH + INTERVAL (x*y+z-1) DAY) dt FROM
(SELECT 1 x UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8) A,
(SELECT 1 y UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) B,
(SELECT 0 z UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) C) AA
WHERE MONTH(@GivenDate) = MONTH(dt)) cal
WHERE DAYOFWEEK(dt) IN (1,7);
``````

If you want to see the dates themselves, run this

``````SET @GivenDate = DATE(NOW());
SELECT dt,DATE_FORMAT(dt,'%W') DayOfWeek FROM
(SELECT dt FROM (SELECT DISTINCT (MAKEDATE(YEAR(@GivenDate),1) +
INTERVAL (MONTH(@GivenDate)-1) MONTH + INTERVAL (x*y+z-1) DAY) dt FROM
(SELECT 1 x UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8) A,
(SELECT 1 y UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) B,
(SELECT 0 z UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) C) AA
WHERE MONTH(@GivenDate) = MONTH(dt)) cal
WHERE DAYOFWEEK(dt) IN (1,7);
``````

If you want to see the count of Saturdays and Sundays separated with a sum, do this

``````SET @GivenDate = DATE(NOW());
SELECT DATE_FORMAT(dt,'%W') DayOfWeek,COUNT(1) DayCount FROM
(SELECT dt FROM (SELECT DISTINCT (MAKEDATE(YEAR(@GivenDate),1) +
INTERVAL (MONTH(@GivenDate)-1) MONTH + INTERVAL (x*y+z-1) DAY) dt FROM
(SELECT 1 x UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6
UNION SELECT 7 UNION SELECT 8) A,
(SELECT 1 y UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) B,
(SELECT 0 z UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) C) AA
WHERE MONTH(@GivenDate) = MONTH(dt)) cal
WHERE DAYOFWEEK(dt) IN (1,7) GROUP BY DayOfWeek WITH ROLLUP;
``````

Give Them a Try !!!