Finding absent employees between date range

Posted by Jp_sist under Sql Server on 3/15/2010 | Views : 1915 | Status : [Member] | Replies : 2
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




Responses

Posted by: Vuyiswamb on: 3/18/2010 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Do you still have this challenge?

Thank you for posting at Dotnetfunda
[Administrator]

Jp_sist, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jp_sist on: 3/24/2010 [Member] Starter

Up
0
Down

No thanks, I myself found out a solution.


Jp_sist, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response