I have a query that returns the availability of an employee (only days they are available):
SELECT emp_id, emp_number, area, day_date, shift_type, start_time, finish_time FROM EmployeeAvailability
I also have a query that gives me all available working days between two dates, which I have written as a CTE:
WITH AvailableDates AS (SELECT DATETIMEFROMPARTS(DATEPART(YEAR,AvailableDate) ,DATEPART(MM,AvailableDate) ,DATEPART(DAY,AvailableDate) ,0,0,0,0) as AvailableDate FROM ( SELECT TOP(DATEDIFF(DAY, '2019-01-14 00:00:00.000', '2019-01-21 00:00:00.000') + 1) AvailableDate = DATEADD(DAY, ROW_NUMBER() OVER( ORDER BY a.object_id) - 1,'2019-01-14 00:00:00.000') FROM sys.all_objects a CROSS JOIN sys.all_objects b) AvailDates )
I need to check the employee availability against the dates available and create an unavailable record for the days they don't have a scheduled shift. However when I join on those dates I am getting the following.
My thoughts are to write a nested cursor to check date and employee but is there a better way to get
availableDate to create a record?
Thanks in advance