CODEWITHSUNDEEP
mysql
xGeo
xGeo

Reputation: 2139

MySQL: Count days within a given range excluding weekends, include start and end if they are weekdays

I know this has been answered in some posts but my requirement is rather different.

What I want is to count all weekdays within range which will include start date and end date. For example:

@s = 2017-05-15, @e = 2017-05-30

using this query (which i found on here):

5 * (DATEDIFF(@E, @S) DIV 7) + MID('0123444401233334012222340111123400001234000123440', 7 * WEEKDAY(@S) + WEEKDAY(@E) + 1, 1)

this returns 11 but what I expect is 12:

[15, 16, 17, 18, 19, 22, 23, 24, 25, 26, 29, 30]

maybe either start or end date is not counted?

Anyone have an idea?

Upvotes: 2

Views: 560

Answers (1)

xGeo
xGeo

Reputation: 2139

So for my requirement, this should be the matrix:

 | M T W T F S S
-|--------------
M| 1 2 3 4 5 5 5
T| 5 1 2 3 4 4 4
W| 4 5 1 2 3 3 3
T| 3 4 5 1 2 2 2
F| 2 3 4 5 1 1 1
S| 1 2 3 4 5 0 0
S| 1 2 3 4 5 5 0

which translate into this string:

1234555512344445123333451222234511112345001234550

Finally, this query solves my problem:

SET @s = STR_TO_DATE('2017-5-01', '%Y-%m-%d');
SET @e = STR_TO_DATE('2017-5-31', '%Y-%m-%d');
SET @weekDays = 5 * (DATEDIFF(@e, @s) DIV 7) + MID('1234555512344445123333451222234511112345001234550', 7 * WEEKDAY(@s) + WEEKDAY(@e) + 1, 1);

This gives me correct result, which is count all weekdays from start date (@s) tp end date (@e).

Hope someone would find this helpful in the future.

Upvotes: 1

Related Questions