0

I need to get the number of working days for each month to date. For example, if today is the 25th of August, I have to calculate (25-(sum of holidays)).

However, for some archaic reason the table only saves Sundays and national holidays, despite that Saturdays are also off days. The table cannot be edited.

So I need to find a way to also count the Saturdays. Since Saturdays and Sundays always come in pair and Saturdays come before Sundays, I can just double the number of Sundays for each month.

The table of holidays (t_holiday) is saved as such:

|    date    |   desc   |
|2016-01-01  |New Year's|
|2016-01-03  |  Sunday  |
|2016-01-10  |  Sunday  |
|2016-01-17  |  Sunday  |
|2016-01-24  |  Sunday  |
|2016-01-31  |  Sunday  |

I could come up with the the queries for just counting the non-weekend holidays and the weekends separately, but I have no idea on how to combine them in one query.

Holidays (non-weekend)

SELECT ((25) -COUNT(date))
FROM t_holiday 
WHERE date between '2016-01-01' and '2016-01-31'
AND desc != 'Sunday'

Weekends (Saturdays and Sundays)

SELECT ((25) - (2*COUNT(holiday_date)))
FROM t_holiday 
WHERE date between '2016-01-01' and '2016-01-31'
AND desc = 'Sunday Off'

Table Structure

tadman
  • 208,517
  • 23
  • 234
  • 262
Rheine
  • 77
  • 1
  • 8
  • I'm not sure you can just double the sundays. What if one saturday is already in a previous month? (not always the same number of sundays and saturdays each month..) – trainoasis Aug 25 '16 at 06:48
  • 2
    Are you using `mysql` or `SQL-Server`? You should really have a calendar table. – NickyvV Aug 25 '16 at 07:22
  • Make sure you use the correct string – Strawberry Aug 25 '16 at 07:24
  • 1
    `SELECT sum(case when desc='Sunday' and day(date)>1 then 2 else 1 end) FROM t_holiday WHERE date between '2016-01-01' and '2016-01-31' ` get count of non working days (for MySQL). – Mike Aug 25 '16 at 07:30

0 Answers0