Overlapping and NonOverlapping dates.. (1 Viewer)

[raghuprabhu]

I have a table with the following fields

surName Text
commDate Date
ceaseDate Date
tableID Autonumber (Key)

The following are some sample records.

surName commDate ceaseDate ID
-------------------------------------------
Prabhu 01-Jan-12 15-Jan-12 1
Prabhu 16-Jan-12 24-Feb-12 2
Prabhu 18-Jan-12 21-Jan-12 3
Prabhu 16-Jan-12 21-Feb-12 4
Prabhu 19-Jan-12 20-Feb-12 5
Prabhu 01-Feb-12 20-Feb-12 6
Prabhu 01-Mar-12 31-May-12 7
Prabhu 12-Apr-12 30-Apr-12 8
Prabhu 15-Jun-12 30-Jun-12 9

I want to design a query named "OverlappingDates" which should show all the records 2 to 8 and another one named "nonOverlappigDates" which should show recrods 1 and 9.

Any ideas?

Thanks in advance.

Raghu Prabhu
Melbourne.

 

[Taruz]

Hi..:

for OverlappingDates..:

[B][I][COLOR="Navy"]select 
surName
, commDate
, ceaseDate
, ID
from tablename
where 
ID<>(select last(ID) from tablename) 
   and 
ID<>(select first(ID) from tablename)[/COLOR][/I][/B]

this for nonOverlappigDates:

[B][I][COLOR="Indigo"]select 
surName
, commDate
, ceaseDate
, ID
from tablename
where 
ID =(select last(ID) from tablename) 
   or 
ID =(select first(ID) from tablename)[/COLOR][/I][/B]

i not limit count of records..

 

[philben]

Hi,

Overlapping query (qrOverlapping) :

SELECT DISTINCT T.*
FROM overlapping AS T, overlapping AS T2
WHERE (T.commDate<=t2.commdate And T.ceaseDate>=t2.commdate And T.ID<>T2.ID) Or
      (T.commDate Between t2.commdate And t2.ceaseDate And T.ID<>T2.ID) Or
      (T.ceaseDate Between t2.commdate And t2.ceaseDate And T.ID<>T2.ID);

NonOverlapping query :

SELECT overlapping.*
FROM overlapping LEFT JOIN qrOverlapping ON overlapping.ID = qrOverlapping.ID
WHERE qrOverlapping.ID Is Null;

regards,

Philippe

 

[philben]

it's possible to improve the performance of the first query (qroverlapping) :

SELECT T.Id, T.surName, T.commDate, T.ceaseDate
FROM overlapping AS T, overlapping AS T2
WHERE (T.commDate<=[t2].[ceaseDate] AND T.ceaseDate>=[t2].[commdate] AND T.ID<>[T2].[ID])
GROUP BY T.Id, T.surName, T.commDate, T.ceaseDate;

 

[raghuprabhu]

Hi philben,

I have attached the database with the table. I have tried to do the query and it is not working. May be my syntext is incorrect.

Please have a look my attached database.

Thanks

Raghu

 

[philben]

I added queries to your database.

See attached.

Philippe

 

[raghuprabhu]

Thanks a lot philben!

I was able to use your designed query and make my life a bit easier.

Well, I am still learning Access and will be learning till my last breath!

Cheers
Raghuprabhu