How to create an attendance sheet in SQL Server 2008?

Data and Databases
1

I’m trying to make an attendance sheet using SQL Server 2008. I have three tables:

  1. Workers:
WorkerID | WorkerName
1        | 'John Doe'
2        | 'Jane Smith'
  1. RestDays:
RestDayID | RestDayName | RestDayCode
1         | 'Saturday'  | 6
2         | 'Sunday'    | 7
  1. Presence:
PresenceID | WorkDate     | WorkerID
1          | '2023-09-01' | 1
2          | '2023-09-02' | 1
3          | '2023-09-03' | 1
4          | '2023-09-04' | 1
5          | '2023-09-05' | 1

John Doe (WorkerID 1) didn’t come to work on Friday (2023-09-08) and Monday (2023-09-11). I want to show Saturday (2023-09-09) and Sunday (2023-09-10) as absent days too.

Can anyone help me write a query to do this? I’ve tried using a stored procedure, but it’s not working as expected. Thanks!

2

hey ryan, i think a calendar table does the trick.
try generating dates and joining them to your workers and presence tables to catch absences on all days. what u reckon?

3

yo ryan, heres an idea: use a CTE to generate dates for ur range. then left join it with workers n presence tables. where presence is null = absent. filter out rest days if u want. lemme know if u need more help!

4

To create an effective attendance sheet, consider using a combination of Common Table Expressions (CTEs) and LEFT JOINs. Start by generating a date range CTE for the desired period. Then, cross join it with the Workers table to create all possible worker-date combinations. LEFT JOIN this result with your Presence table to identify absences. Finally, use CASE statements to categorize each day as present, absent, or rest day based on the RestDays table. This approach provides a comprehensive view of attendance, including weekends and other rest days, without relying on stored procedures. It’s scalable and can easily accommodate multiple workers and varying date ranges.