SQL Server selecting a foreign key whose value doesn't appear for given conditions

Question

i am attempting to write a query as part of a music school which will select only tutors which are available at a certain time on a given day, lessons can be booked in blocks of ten twenty or thirty and as such i need to check either 10, 20 or 30 weeks to see if the lesson wanted is free on all of the corresponding days, the query i am currently using is not allowing lessons within the time period but should be allowing them on any day of the week except the one booked, i hope that is coherent

select Tutor.TutorId
from Tutor 
left Join Booking on Tutor.TutorId=Booking.TutorId
where Tutor.Instrument='Keyboard' 
    and Booking.StartTime!='13:00:00' 
    and '2017-03-16' NOT between Booking.StartDate and DATEADD(dd,70, Booking.StartDate)  
    and ABS((DATEDIFF(DD,'2017-03-16',Booking.StartDate)))%7 =0 
                   or
        Booking.StartTime is null 
    and Tutor.Instrument='Keyboard' 
                   or
       Booking.StartDate NOT between '2017-03-16' and DATEADD(dd,70, '2017-03-16') 
    and Tutor.Instrument='Keyboard' 
    and ABS((DATEDIFF(DD,'2017-03-16',Booking.StartDate)))%7 =0

score 1 · Answer 1 · edited Jul 29 '20 at 18:42

You should wrap your or conditions in parenthesis for clarity and to ensure that operators are being evaluated in the order you are intending.

I think you are trying to do this:

select Tutor.TutorId
from Tutor
  left join Booking
    on Tutor.TutorId = Booking.TutorId
where Tutor.Instrument = 'Keyboard'
  and abs((datediff(day, '20170316', Booking.StartDate))) % 7 = 0
  and (Booking.StartTime != '13:00:00'
    or Booking.StartTime is null)
  and ('20170316' not between Booking.StartDate and dateadd(day, 70, Booking.StartDate)
    or Booking.StartDate not between '20170316' and dateadd(day, 70, '20170316')
      )

Reference:

Note: You should avoid using shorthand for date/time operations, you should write your string dates as 'YYYYMMDD' without the hyphens, and be careful using between as it may not be doing what you think it is doing:

You can simplify your query by using a calendar table or by generating a sequence of dates for the number of weeks you are checking for, and using `not exists()` instead of a `left join`.

In the not exists() we will join the Booking table to our list of dates, and check the StartTime for those bookings, for each TutorId in Tutors. This will only return those tutors that have no conflict with the desired schedule.

/* variables */
declare @fromdate date = '20170316';
declare @weeks int = 10;
/* stacked cte to generate one date for @weeks number of weeks */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (@weeks) 
    [Date]=convert(date
          ,dateadd(week, row_number() over (order by (select 1)) -1, @fromdate)
          )
  from n as deka cross join n as hecto cross join n as kilo cross join n as tenK
  order by [Date]
)
/* rewritten query to use not exists */
select t.TutorId
from Tutor as t
where t.Instrument = 'Keyboard'
  and not exists (
    select 1
    from Booking as b
      inner join dates as d
       on b.StartDate = d.[Date]
    where b.TutorId = t.TutorId
      and b.StartTime = '13:00:00'
  );

is there anyway the second query can be rewritten to ensure it checks dates in the future and past? — jack ward, Mar 12 '17 at 21:26
@jackward I don't see why it would matter if it was future or past, just change the values for the two variables at the beginning. — SqlZim, Mar 12 '17 at 23:52

SQL Server selecting a foreign key whose value doesn't appear for given conditions

1 Answers1