I have an Attendance table with columns as:
DATE- shows all dates of a month
STATUS- present/absent
IN - in time of employee
OUT - out time of employee
WEEKENDS - displays 'NR' for weekends(i.e saturday and sunday) and '-' for other days in a particular
HOLIDAY - displays 'H' for any national holiday(other than weekends) and '-' for other days
I want to calculate the number of working days in a particular month excluding weekends and holidays and have used the query as:
count(*)-(count(weekends)+count(holiday)).
This gives me the result which is correct in some cases and wrong in cases where the holiday falls on either saturday or sunday.
For e.g.
In December-2016 13/12/2016 and 25/12/2016 are holidays (as per Indian calendar) and also 25/12/2016 is Sunday. So the working days according to my query results in '20' which should actually be '21' (i.e 31[days in December] - 9[weekends] -1[holiday on 13th Dec]) because it calcluates 25th Dec two times in count(weekend) and count(holiday) too which is wrong.
Kindly help.
And I would like to mention that I have to find and display the working days, presentDays, absentDays based on above table in single SELECT query
How do i check whether the date of holiday and saturday/sunday is same and exclude that holiday?