CODEWITHSUNDEEP
sql
Kevin Marchand
Kevin Marchand

Reputation: 45

SQL Find Datetime outside Datetime range

I have 2 tables one called Production and the other called Schedule. I am trying to find is there is some production outside the schedule. So far I am getting duplicated value because the production could be inside one schedule but outside the other one. So far I have no luck with this sql query I was wondering if someone can point me to the right direction.

thanks in advance.

SELECT TB1.*
FROM Production AS TB1
INNER JOIN Schedule AS TB2
ON TB1.ProduceDate < TB2.StartDate OR TB1.ProduceDate > tb2.EndDate
GROUP BY TB1.ID,TB1.ProduceDate
ORDER BY Tb1.ProduceDate

ID  Produce Date
1   2017-02-03 09:00:00.000
2   2017-02-03 11:00:00.000
3   2017-02-03 13:00:00.000
4   2017-02-03 18:00:00.000
7   2017-02-03 19:00:00.000
5   2017-02-03 20:00:00.000
6   2017-02-03 23:00:00.000

Production Table Data

ID  ProduceDate
1   2017-02-03 09:00:00.000
2   2017-02-03 11:00:00.000
3   2017-02-03 13:00:00.000
4   2017-02-03 18:00:00.000
5   2017-02-03 20:00:00.000
6   2017-02-03 23:00:00.000
7   2017-02-03 19:00:00.000

Schedule Table Data

ID  StartDate               EndDate
1   2017-02-03 10:00:00.000 2017-02-03 12:00:00.000
2   2017-02-03 15:00:00.000 2017-02-03 19:00:00.000

Upvotes: 0

Views: 37

Answers (2)

JohnHC
JohnHC

Reputation: 11195

select Production.*
from Production
left join Schedule
  on ProduceDate between StartDate and EndDate

where Schedule.id is null

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think you just want not exists:

select p.*
from production p
where not exists (select 1
                  from schedule s
                  where p.producedate >= s.startdate and
                        p.producedate <= s.enddate
                 );

Upvotes: 2

Related Questions