How to find Employees not scheduled between date range picker dates

Queries and Resources
1

Hello and thanks in advance for any help!
I am trying to find employees not scheduled between a certain date range using a date range component.
I have a roster table that contain a column called first_last that links to another table called scheduled_assignments
image

scheduled_assignements table that contains a column called first_last and week
image

I want to return first_last if an employee is not scheduled between a date range
image

Below is the code that i have written so far, but it is not returning any results

SELECT
roster.first_last
FROM
roster
LEFT OUTER JOIN schedule_assignments ON roster.first_last = schedule_assignments.first_last
WHERE
schedule_assignments.first_last is null AND
(schedule_assignments.week <= {{moment(dateRange1.value.start).format('YYYY-MM-DD')}}
AND schedule_assignments.week >= {{moment(dateRange1.value.end).format('YYYY-MM-DD')}})

2

Hello,
I am still struggling with finding a solution. I have tried all methods of formatting with no luck. Any advice will help!

3

You may need a subquery for the assignments rows to narrow the date range prior to the join. Something like:

SELECT
roster.first_last
FROM
roster
LEFT JOIN (
   select 
      * 
   from schedule_assignments 
WHERE
schedule_assignments.week between {{moment(dateRange1.value.start).format('YYYY-MM-DD')}}
AND {{moment(dateRange1.value.end).format('YYYY-MM-DD')}}) as sub_q
ON roster.first_last = sub_q.first_last
WHERE
sub_q.first_last is null
2 Likes
4

Thank you @kschirrmacher ! This solution worked out great!

1 Like