Employee Shift Check - Nested Cursor

Question

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

Answer 1

So you want one row for each (employee,date), with shift details filled in for days on which the employee has a shift?

For this kind of problem use the "cross join, left join" pattern. Start with a cross join that establishes the granularity of the result set, and then left join to the matching rows. Something like:

SELECT 
    e.emp_id, 
    e.emp_number, 
    a.day_date,
    ea.area, 
    ea.shift_type,
    ea.start_time, 
    ea.finish_time 
FROM Employees e
CROSS JOIN AvailableDates a
LEFT JOIN EmployeeAvailability ea
   on a.AvailableDate = ea.day_date
   and e.emp_id = ea.emp_id