Research Development Research Development - 4 months ago 36
SQL Question

how to calculate total number of saturday and sunday between two dates in mysql

SELECT
floor(
datediff('2016-08-01','2016-07-01') / 7) * 2 + (
CASE WHEN
IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) >=
IF (weekday('2016-07-02') >= 5, 4,weekday('2016-07-01'))
THEN

IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) -
IF (weekday('2016-07-01') >= 5, 4,weekday('2016-07-01'))
ELSE
5 +
IF (weekday('2016-08-01') >= 5, 4,weekday('2016-08-01')) -
IF (weekday('2016-07-01') >= 5, 4, weekday('2016-07-01'))
END
) weekdays;


when i execute this code it always return 9 i don't know where i am doing mistake please suggest me where am doing wrong. while between Saturday and Sunday between given date should be 10 please suggest me

Answer

If you don't have a table having all the dates between your start date and end date (inclusive) then you need to adopt a query which will create all the dates between your given date range (inclusive) first. Then use WEEKDAY function of MySQL to check whether the day is Saturday or Sunday

SELECT 
dateTable.Day,
DAYNAME(dateTable.Day) AS dayName
FROM 
(   SELECT ADDDATE('2016-07-01', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-01', '2016-07-01')

) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6)
ORDER BY dateTable.Day;

WORKING DEMO

Note: WEEKDAY returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

EDIT:

If you need only count:

SELECT 
COUNT(*) AS total
FROM 
(   SELECT ADDDATE('2016-07-01', INTERVAL @i:=@i+1 DAY) AS DAY
    FROM (
        SELECT a.a
        FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
        CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
    ) a
    JOIN (SELECT @i := -1) r1
    WHERE 
    @i < DATEDIFF('2016-08-01', '2016-07-01')

) AS dateTable
WHERE WEEKDAY(dateTable.Day) IN (5,6)

Demo