Hello Frnds,
Am in need of ur help. Plz suggest if u can answer for this...
I have tables as listed below
tblEmp, tblAttendance, tblLeave, tblStateWiseHoliday,tblState
tblEmp - contains the list of employees (empid, statename)
tblAttendance - contains the list of Daily entry [an entry for an emp per day] (empid, date,..)
tblLeave - contains the list of leaves taken by emp (empid, stdate, enddate,..)
tblStateWiseHoliday - contains the list of holidays for the year (StateId, hoilydayId, Date)
tblState - contains the list of States (StateId, StateName)
tblHoliday - contains list of Holidays (not required for this condition)
Mar01, Mar02, Mar03, Mar04, Mar05, Mar06, Mar07, Mar08, Mar09, Mar10, Mar11, Mar12, Mar13, Mar14, Mar 15
E1 P P L P A P SUN L H L P P A P A
E2 A L P A H P SUN L H L P P A P L
E3 P P L P P H SUN L H P A P L A P
P - entries in the tblAttendance
L - entries in the tblLeave
A - no entries in any tables
H - entries in the tblStateWiseHoliday as per Emp's State
SUN - no entries in any table.
Now, i want to populate the list of employees absent details which ignores the holidays (H), sundays (SUN), leaves (L), present (P)
In the above case, am passing the date range between Mar 01 to Mar 14
------------------------------------------------------------------------
E1 - Mar05, Mar, Mar13
E2 - Mar01, Mar04, Mar13
E3 - Mar11, Mar14