I have a query that returns the availability of an employee (only on days when they are available).
SELECT emp_id, emp_number, area, day_date, shift_type, start_time, finish_time FROM employee availability
I also have a query that gives me all available workdays between two appointments that I have written as CTE.
WITH AvailableDates AS (SELECT DATETIMEFROMPARTS (DATEPART (YEAR, Available Date)) , DATEPART (MM, available date) , DATEPART (DAY, AvailableDate) , 0,0,0,0) as available date FROM ( SELECT TOP (DATEDIFF (DAY, & # 39; 2019-01-14 00: 00: 00.000 & # 39 ;, & # 39; 2019-01-21 00: 00: 00.000 & # 39;) + 1) AvailableDate = DATEADD (DAY, ROW_NUMBER () OVER (ORDER BY a.object_id) - 1, & # 39; 2019-01-14 00: 00: 00.000 & # 39;) FROM sys.all_objects a CROSS JOIN sys.all_objects b) Availability data )
I need to check staff availability against available data and create an unavailable record for the days they do not have a scheduled shift
But when I join these dates, I get the following.
My thoughts are to write a nested cursor to check the date and the employee. Is there a better way to create a record using emp_id, emp_Number, area, and availableDate?
thank you in advance