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 emp_id
, emp_Number
, area
and availableDate
to create a record?
Thanks in advance