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) 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
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.
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 🙂