1

This is getting the best of me!!

I created calendar table (table1) loaded with all days for a period of 5 years. Among other things the table includes the date, the day type (weekend, holiday etc.) Important fields here are table1.date and table1.type

I have a second table (table2) that records days worked. Important fields here are table2.date and table2.employee

I want to identify which dates were not worked by an employee excluding any days that might be a weekend or holiday since those are not required work days.

table1
=======
2/10/20 Weekday
2/11/20 Weekday
2/12/20 Weekday
2/13/20 Weekday
2/14/20 HOLIDAY
2/15/20 Weekend
2/16/20 Weekend

table2
=======
2/10/20   john
2/10/20   jimmy
2/10/20   mary
2/11/20   john
2/11/20   jimmy
2/11/20   mary
2/12/20   john
2/12/20   mary
2/13/20   jimmy
2/13/20   mary
2/14/20   jimmy

Query result should show days that do not exist in table2.

Jimmy 2/12/20  
John 2/13/20
Improve this question
2

You can do this using the EXCEPT operator to remove the weekdays worked from a CROSS JOIN query that returns all possible weekdays worked by each employee.

SELECT DISTINCT wr.employee, c.work_date
FROM cal c
CROSS JOIN work_record wr 
WHERE c.day_type = 'Weekday'
EXCEPT
SELECT employee, date_worked 
FROM work_record wr

DDL for creating the tables and data:

CREATE TABLE [cal](
    [work_date] [date] NULL,
    [day_type] [varchar](20) NULL
) ON [PRIMARY]
GO

CREATE TABLE [work_record](
    [date_worked] [date] NULL,
    [employee] [varchar](20) NULL
) ON [PRIMARY]
GO

INSERT [cal] ([work_date], [day_type]) 
VALUES (CAST(N'2020-02-10' AS Date), N'Weekday'),
(CAST(N'2020-02-11' AS Date), N'Weekday'),
(CAST(N'2020-02-12' AS Date), N'Weekday'),
(CAST(N'2020-02-13' AS Date), N'Weekday'),
(CAST(N'2020-02-14' AS Date), N'HOLIDAY'),
(CAST(N'2020-02-14' AS Date), N'Weekend'),
(CAST(N'2020-02-14' AS Date), N'Weekend')

INSERT [work_record] ([date_worked], [employee]) 
VALUES (CAST(N'2020-02-10' AS Date), N'john'),
(CAST(N'2020-02-10' AS Date), N'jimmy'),
(CAST(N'2020-02-10' AS Date), N'mary '),
(CAST(N'2020-02-11' AS Date), N'john '),
(CAST(N'2020-02-11' AS Date), N'jimmy'),
(CAST(N'2020-02-11' AS Date), N'mary '),
(CAST(N'2020-02-12' AS Date), N'john '),
(CAST(N'2020-02-12' AS Date), N'mary '),
(CAST(N'2020-02-13' AS Date), N'jimmy'),
(CAST(N'2020-02-13' AS Date), N'mary '),
(CAST(N'2020-02-14' AS Date), N'jimmy')
Improve this answer
  • I already have the table of dates and dont care to use CTE - will this same concept work using the existing table? – BoomerBo May 6 '20 at 16:39
  • I updated the answer to remove the CTE, but it essentially is the same logic. You have to somehow materialize records for days that the employees did not work, so that is why the first query uses a CROSS JOIN. – Tony Hinkle May 6 '20 at 16:52
  • This looks promising, but I found an issue - not everyone in the work record table is an employee anymore. It goes back to 2010 and the setup above pulls all those old employees in as not working on that date range. How might I/we/you (thanks) query only a specific date range for example BETWEEN 2/1/10 AND 2/14/20 and then Im going have to join somewhere a third table I hadnt thought of... the employee directory it has a fkey for employee and a column for userretired. So join / exclude directory.employee where employee.userretired = true ... – BoomerBo May 6 '20 at 18:46
  • OK--you'll need to provide the complete requirements if you need a complete answer. To have it run only for current employees, you'll need to join it to an employees table and filter the results by termination_date colum or active column or whatever is available to determine if they are a current employee. – Tony Hinkle May 6 '20 at 18:51
  • I got it to work - THANKS a million!!!! I first joined the work record to the employee record to filter out retired employees and then used that in the except section - works prefrectly ! – BoomerBo May 7 '20 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.