I have written the following query in SQL Server
(QUERY)
select ISNULL(count(WORKHOUR) ,'-') AS present,
ISNULL((count(*)-count(WORKHOUR)),'-') AS absent,
ISNULL(cast(SUM(DateDiff(MINUTE, CAST('0:00' as time), WORKHOUR))/60 as nvarchar(3)) + ':' + cast(SUM(DateDiff(MINUTE, CAST('0:00' as time), WORKHOUR))%60 as nvarchar(2)),'-') AS TOTALWRKHRS,
ISNULL(cast(AVG(DateDiff(MINUTE, CAST('0:00' as time), WORKHOUR))/60 as nvarchar(3)) + ':' + cast(AVG(DateDiff(MINUTE, CAST('0:00' as time), WORKHOUR))%60 as nvarchar(2)),'-') AS AVGWRKHRS
from "+**currMonthVal**+" m,"+**currYearVal**+" e WHERE E.EMPID=M.EMPID AND E.LOCATION=M.LOCATION AND M.EMPID = ?
and DATENAME(WEEKDAY, attdate) not IN ('Saturday', 'Sunday') and CONVERT(VARCHAR(10),ATTDATE,3) not in ("+**resultantHL**+")
where currMonthVal and currYearVal are Month and Year values passed from outside and resultantHL are the holiday_dates (like '24/12/2016','25/12/2016') passed from outside.
This query works fine.
I have a table named WORKING_DAYS as follows:'
COLUMN_ID COLUMN_NAME DATA_TYPE --------------------------------------- 1 ID NUMBER 2 WDATE DATE 3 INSTITUTE_ID NUMBER
and sample data in table is:
COLUMN_ID WDATE INSTITUTE_ID --------------------------------------- 1 05-12-15 1 2 19-11-16 1
These two dates in table are actually declared as extra working days in respective months and fall on either Saturday or Sunday.
My requirement is : I have excluded all Saturday and Sunday from the month in the query above [using DATENAME(WEEKDAY, attdate) not IN ('Saturday', 'Sunday')] but I do not want to exclude the above two dates in WORKING_DAYS table but include them And I want to do that in the same query if possible as that too in WHERE clause.
How can I do so?
Note: I have already retrieved the extra working days from WORKING_DAYS table and stored in a string variable and wish to pass that variable value in the condition
