Home » SQL & PL/SQL » SQL & PL/SQL » Date Between Help
Date Between Help [message #294493] Thu, 17 January 2008 15:36 Go to next message
lowcash75
Messages: 67
Registered: February 2006
Member
I have a table that contains arrival dates and dep dates. I'm trying to generate a report to list who came here during a certain time period. What is the best way to run this query.
Below is the script to create the table and data.

I'm trying to return records that fall in between Oct 1, 2006 and Sep 30, 2007. (ID- 1,2 & 4)

create table test (id number, arrive_dt date, dep_dt date);

insert into test
(ID,ARRIVE_DT,DEP_DT)
select
1,to_date('11/01/2006','MM/DD/YYYY'),to_date('11/03/2006','MM/DD/YYYY') from dual
/
insert into test
(ID,ARRIVE_DT,DEP_DT)
select
2, to_date('10/1/2005','MM/DD/YYYY'),to_date('11/10/2008','MM/DD/YYYY') from dual
/
insert into test
(ID,ARRIVE_DT,DEP_DT)
select
3,to_date('1/1/2004','MM/DD/YYYY'),to_date('1/1/2005','MM/DD/YYYY') from dual
/
insert into test
(ID,ARRIVE_DT,DEP_DT)
select
4,to_date('12/1/2006','MM/DD/YYYY'),to_date('11/10/2007','MM/DD/YYYY') from dual
/

Thanks!
Re: Date Between Help [message #294497 is a reply to message #294493] Thu, 17 January 2008 16:17 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm afraid I didn't understand the question.

This is the data (all dates 'dd.mm.yyyy'):
SQL> select * From test;

              ID ARRIVE_DT  DEP_DT
---------------- ---------- ----------
               1 01.11.2006 03.11.2006
               2 01.10.2005 10.11.2008
               3 01.01.2004 01.01.2005
               4 01.12.2006 10.11.2007

Quote:
list who came here during a certain time period
"Came here" means "arrive date", doesn't it? If so, this is how a query looks like:
SQL> select * From test
  2  where arrive_dt between to_date('01.10.2006', 'dd.mm.yyyy')
  3                      and to_date('30.09.2007', 'dd.mm.yyyy');

              ID ARRIVE_DT  DEP_DT
---------------- ---------- ----------
               1 01.11.2006 03.11.2006
               4 01.12.2006 10.11.2007

SQL>
As record ID=2 isn't among the selected values ("(ID- 1,2 & 4)"), I must be wrong. However, you might try to explain once again what exactly means "coming here" and which date(s) should fall into period you mentioned.
Re: Date Between Help [message #294498 is a reply to message #294497] Thu, 17 January 2008 16:22 Go to previous messageGo to next message
lowcash75
Messages: 67
Registered: February 2006
Member
That's the query I had also, but I need to include ID # 2 because the range Oct 1, 2006 thru Sep 30, 2007 fall within arrival dt (01.10.2005) & departure date(10.11.2008).

So basically need any range of dates from the 4 records that fall within Oct 1, 2006 thru Sep 30, 2007 (ID's-->1,2 & 4)

[Updated on: Thu, 17 January 2008 17:11]

Report message to a moderator

Re: Date Between Help [message #294525 is a reply to message #294498] Thu, 17 January 2008 22:36 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Why did not you specify in your first post, using "came" was inaccurate, as the row 2 "came" before (NOT during) the given range.

Now, if I understand you, you want all intervals (ranges) in the table which overlap the given one (INT_MIN, INT_MAX), in your case (Oct 1, 2006, Sep 30, 2007). That is simple logic:
ARRIVE_DT <= INT_MAX AND (DEP_DT >= INT_MIN OR DEP_DT IS NULL)
In words: arrival was before the 'range end' and departure happened after the 'range start' or did not happen at all (you may remove the last part if DEP_DT is always filled, I just used it for completeness).
Previous Topic: Table of Table Definitions
Next Topic: Results sorting from the middle outward
Goto Forum:
  


Current Time: Thu Dec 08 08:22:55 CST 2016

Total time taken to generate the page: 0.08402 seconds