query to find the date/s that each user missing to signed in his attendance

Question

Post reply

query to find the date/s that each user missing to signed in his attendance

tanehome

Ten Centuries

Points: 1167
More actions
October 11, 2023 at 4:20 pm

#4306017
Good Day Everybody,
I've missing something in this query but I can't catch it, it keeps give me error:
Msg 102, Level 15, State 1, Line 36
Incorrect syntax near ')'.
the query to find the date/s that each user not signed in his attendance and not requested as leave moreover not a holiday nor Friday
as i have four tables:
1-users table contains all users data such as number, name, email, department
2-leaves table containing users leave requests data such as user number, name, leave start data and leave end date.
3-attendance table recording users attendance data on each working day such as user number, name, date & time
4-holidays table containing holidays dates.
noting that the leaves table will be filled with record for the not attending day only when the user requesting the leave.
noting that both users and leaves tables in DB1 while attendance and holiday tables in another database DB2
many thanks,
```
WITH MissingAttendance AS (
  SELECT
    u.user_number,
    u.name,
    a.date
  FROM DB1.users u
  CROSS JOIN DB2.attendance a
  WHERE NOT EXISTS (
    SELECT 1
    FROM DB2.attendance a2
    WHERE a2.user_number = u.user_number
      AND a2.date = a.date
  )
  AND NOT EXISTS (
    SELECT 1
    FROM DB1.leaves l
    WHERE l.user_number = u.user_number
      AND l.leave_start_date <= a.date
      AND l.leave_end_date >= a.date
  )
  AND DATEPART(dw, a.date) != 6 -- Ensure it's not a Friday (assuming Sunday=1, Monday=2, ..., Saturday=7)
  AND NOT EXISTS (
    SELECT 1
    FROM DB2.holidays h
    WHERE h.date = a.date
  )
)
```
- This topic was modified 1 year, 1 month ago by tanehome.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

You've declared a CTE, but then you do nothing with it.

Add SELECT * FROM MissingAttendance, for example, at the end and it should be fine.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101096 More actions · Answer 2

And to make the code not reliant on a particular DATEFIRST setting, don't use DATEPART, instead do this:

...
AND DATEDIFF(DAY, 0, a.date) % 7 <> 4 -- Ensure it's not a Friday (Monday=0,Tue=1,...,Sun=6)
...

That will work accurately for any/all DATEFIRST settings.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".