CODEWITHSUNDEEP
sqlsql-server
Gourav khanna
Gourav khanna

Reputation: 79

How to compare dates from one table with another table

I am facing one problem in sql queries, Here is the solution i tried:

CREATE TABLE #EventTable(eStartDate datetime,eEndDate datetime)
insert into #EventTable VALUES('2014-10-21 10:57:48.603','2014-10-22 10:57:48.603')
insert into #EventTable VALUES('2014-10-23 10:57:48.603','2014-10-23 10:57:48.603')
insert into #EventTable VALUES('2014-10-25 10:57:48.603','2014-10-27 10:57:48.603')
insert into #EventTable VALUES('2014-10-28 10:57:48.603','2014-10-30 10:57:48.603')
insert into #EventTable VALUES('2014-10-31 10:57:48.603','2014-11-02 10:57:48.603')

Second table

CREATE TABLE #NeedDateTable(StartDate datetime,EndDate datetime)
insert into #NeedDateTable VALUES('2014-10-22 10:57:48.603','2014-10-26 10:57:48.603')
insert into #NeedDateTable VALUES('2014-11-23 10:57:48.603','2014-11-23 10:57:48.603')
insert into #NeedDateTable VALUES('2014-11-02 10:57:48.603','2014-11-03 10:57:48.603')

SELECT * from #EventTable
SELECT * from #NeedDateTable

I want to return dates from #NeedDateTable which are overlapping with #EventTable. So in my case it should return two dates "2014-10-22 10:57:48.603 && 2014-10-26 10:57:48.603" and "2014-11-02 10:57:48.603 && 2014-11-03 10:57:48.603"

Here is my query

SELECT Startdate , EndDate 
FROM #NeedDateTable a,#EventTable b  
WHERE ((a.Startdate <= b.eStartDate and b.eStartDate<= a.EndDate) 
OR (a.Startdate <= b.eEndDate and b.eEndDate <= a.EndDate))

It is returning duplicate records for above dates. Could you please help in solving my query.

Upvotes: 1

Views: 164

Answers (4)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Try adding this to your test tables:

insert into #EventTable VALUES('2015-05-10','2015-05-10')
insert into #EventTable VALUES('2016-05-10','2016-05-13')
insert into #NeedDateTable VALUES('2015-05-10','2015-05-10')
insert into #NeedDateTable VALUES('2016-05-11','2016-05-12')

Both examples will overlap, but the other answers will currently fail to identify one of these overlaps.

Here is how to detect overlaps:

SELECT * FROM #NeedDateTable a 
WHERE exists 
(
  SELECT null FROM #EventTable b 
  WHERE 
    a.StartDate <= b.eEndDate and
    a.EndDate >= b.eStartDate
);

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Query

SELECT a.StartDate,a.EndDate
FROM NeedDateTable a
INNER JOIN
EventTable b
ON a.StartDate=b.eStartDate
OR a.StartDate=b.eEndDate
OR a.EndDate=b.eStartDate
OR a.EndDate=b.eEndDate;

Fiddle Demo

Upvotes: 2

AA.SC
AA.SC

Reputation: 377

Use Simple Between statement in your Query 

SELECT  Startdate,
        EndDate
    FROM    #NeedDateTable a cross join
            #EventTable b
    WHERE   a.Startdate BETWEEN b.eStartdate AND b.eEndDate
            OR a.EndDate BETWEEN b.eStartdate AND b.eEndDate

Upvotes: 0

Deep
Deep

Reputation: 3202

what I see in you output that you want records of NeedDateTable which have StartDate = EventTable's eEndDate. if this is the case then here is the query :

select a.* from NeedDateTable a join  EventTable b 
on a.StartDate = b.eEndDate;

OR if you want to return records of NeedDateTable where exists a record in EventTable whose estartdate or eenddate lies between startdate and end date of NeedDateTable, then:

select * from NeedDateTable a 
where exists 
(
  select 1 from EventTable b 
  where (b.eStartDate between a.StartDate and a.EndDate) or
  (b.eEndDate between a.StartDate and a.EndDate)
);

sample sql fiddle

Upvotes: 1

Related Questions