# Employee Shift Check – Nested Cursor

## All we need is an easy explanation of the problem, so here it is.

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)
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?

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 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
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂