5

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.

0

2 Answers 2

8

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)
0
2

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 !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.