AUTOMATIC_DATA_SEARCH_EMP_HOLIDAY_WISE.sql

--- SEARCH WORKER HOLIDAY ATTENDENCE ---

SELECT * FROM TB_DATA_MASTER -- TAKE ATTENDENCE FROM DATA_MASTER TABLE
WHERE -- IN THER WE TRY TO MATCH CARDNO AND HAR HOLIDAY AT A TIME USING IN OPERATOR 
(
		-- NOW, IN FIRST TIME WE TAKE THE DAY NAME OF THIS DATE 
		-- THEN WE TRY TO MATCH THIS DAY NAME MATCH WITH EMP HOLIDAY 
		-- IF EMP HOLIDAY MATCH WITH THIS DAY IT RETURN SIMILAR DATE.  
		-- SUBSTR() FUNCTION HELP'S US TAKE ACTUAL NAME OF THIS DAY. 
		-- TO_CHAR() FUNCTION RETURN A STRING VERSION OF THIS DATE .
		CARDNO, (SUBSTR(TO_CHAR(PDATE, 'DL' ),1,INSTR(TO_CHAR(PDATE,'DL'),',')-1))
) IN -- CHECK CADNO AND PDATE ARE PRESENT IN SUB_QUERY BLOCK 
(
  	  -- SELECT CARDNO AND WK_HOLIDAY NAME FROM TB_PERSONAL_INFO 
	  -- FILTER BY ACTIVE,SECTION, DEPARTMENT 
  	  SELECT CARDNO,WDAY FROM TB_PERSONAL_INFO
	  WHERE ACTIVE=0 
	  AND DEPARTMENTNM LIKE DECODE(NVL(:DEPARTMENT_NAME,'ALL'),'ALL','%',:DEPARTMENT_NAME)
	  AND SECTIONNM LIKE DECODE(NVL(:SECTION_NAME,'ALL'),'ALL','%',:SECTION_NAME)
)
-- FILTER BY FINMONTH AND FINYEAR 
AND FINMONTH LIKE DECODE(NVL(:MONTHS,'ALL'),'ALL','%',:MONTHS) 
AND FINYEAR LIKE DECODE(NVL(:YEARS,'ALL'),'ALL','%',:YEARS)