0

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.

records returned

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

  • Can you provide an INSERT statement for the datas at all? I'd like to spin up a dbfiddle to give this a go. – George.Palacios Jan 17 at 8:18
0

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
  • Thank you! This has been doing my head in all day! – Lucas Perrett Jan 17 at 21:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.