help needed to design payroll policy - i m lost :(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

help needed to design payroll policy - i m lost :(

Author

Topic

style2joy
Starting Member

8 Posts

Posted - 2008-02-12 : 02:18:32

hi,

Please help me in designing one of our payroll policies. i have been trying but unable to find any solution.

i have data in 2 tables :
1. attendance (empid, date, hours, leaveallowed)
2. holidays (date,type) (4 types of holidays like national, weekly offs etc)

Policy: if any employee is not present on both days - the day before and after the holiday then leave allowed is false

attendace table contains the records for all employees who are present and i have to add rows for missing dates where leaveallowed will be false (hours will be zero).

eg. if any employee is absent on friday and monday then leaveallowed for saturday and sunday will be false.

Please mail me at style2joy at gmail .com

thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-02-12 : 03:02:33

What have you got so far?

style2joy
Starting Member

8 Posts

Posted - 2008-02-12 : 06:21:50

i have tried many things but the best i have is following query that uses cross join with holidays table after adding rows for all dates in holidays (holidays table now looks as calendar)


SELECT DRVD.empid,DRVD.calendardate,DRVD.holidaytype,
       attendance.workhours 
FROM attendance RIGHT OUTER JOIN
    (SELECT * FROM employees CROSS JOIN calendar 
         WHERE calendardate < getdate()) DRVD 
ON attendance.empid=DRVD.empid 
   AND attendance.workdate=DRVD.calendardate

style2joy
Starting Member

8 Posts

Posted - 2008-02-14 : 01:09:47

pleaseeeeeeeeeeeeeeeeeeeeee help!!!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources