Please Help To Design A Payroll Policy

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.

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)


Code:

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




thanks