CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Chase Ernst
Chase Ernst

Reputation: 1155

SQL Date that is NOT between (select statement)

I am not sure how to approach this problem that I have encountered. This is what I am trying to do:

I have a table that contains a calendar, so every day of the year (yyyy-mm-dd).

I have another table that contains all days worked by our equipment. This table is populated from another part of my website, but is also in the same format (yyyy-mm-dd).

I have a third table that contains all events in our scheduling system. Through this system our employees can book the days off of our equipment and such.

So what I am trying to do is find all off the days in the Calendar that where not worked by a certain unit:

select *
from Calendar
where PKDate not in (select DayWorked
                from DaysWorked
                where Unit='105')

but I also need to take into account the equipment's days off. I do not want to include these days into my result set. This is the schema of the event(days off) schedule:

ID  | Name        | EventStart  | EventEnd    | UnitID
1     Days Off      2015-2-10     2015-2-15     105
2     Days Off      2015-3-2      2015-3-10     105

All of the date columns are DateTime

I am not exactly how I can use the EventStart and EvetnEnd columns in a NOT IN clause in my SQL query. I am using SQL Server 2012. Any ideas?

Upvotes: 0

Views: 368

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271013

NOT EXISTS is what comes to my mind:

select c.*
from calendar c
where not exists (select 1
                  from DaysWorked
                  where Unit = '105' and c.PKDate = d.DayWorked
                 ) and
      not exists (select 1
                  from DaysOff d
                  where Unit = '105' and
                        c.PKDate >= d.eventstart and c.PKDate <= d.eventend
                 );

Note: If there is the slightest possibility that the dates stored in the tables could have time components, then you need to adjust the queries to take this into account. If they do not have time components, you should use date instead of datetime.

Upvotes: 1

fthiella
fthiella

Reputation: 49089

I would use a LEFT JOIN to exclude all intervals from the day off table:

SELECT Calendar.*
FROM
  Calendar LEFT JOIN DaysOff
  ON Calendar.PKDate BETWEEN DaysOff.EventStart AND DaysOff.EventEnd
     AND UnitID='105'
WHERE
  DaysOFf.ID IS NULL
  AND PKDate NOT IN (SELECT DayWorked
                     FROM DaysWorked
                     WHERE Unit='105')

Upvotes: 3

Related Questions