Home » SQL & PL/SQL » SQL & PL/SQL » Select Query with date comparison
Select Query with date comparison [message #270173] Wed, 26 September 2007 00:56 Go to next message
galaxy
Messages: 62
Registered: October 2005
Member
Hi,

Lets take the following data example extract out of my tables (The real data is different than this). There is a lot more information in the tables than this selection.


Table1

Column3___Column7___Date_Column
BMW________3er_____02/24/2007
BMW________5er_____01/12/2004
...

Table2

Column1___Column2___Stopdate
BMW________3er_____01/12/2007
BMW________3er_____03/11/2007
BMW________3er_____07/12/2007
BMW________3er_____09/17/2007
...



I have already joined both tables in my select query on the fields:

Table1.Column3=Table2.Column1 and
Table1.Column7=Table2.Column3

What I want to have is that my query should only show as a result the datalines where the stopdate is the minimum of all the dates which are higher than the date given in Column Date_Column. It is hard to explain but from this data example I want to receive the following line:

Column3___Column7___Date_Column_____ Stopdate
BMW________3er_____02/24/2007 _______03/11/2007

So the date 03/11/2007 is the date from table2 which is the lowest date from all dates which are higher than the Date given in Table one.

The sense behind this is that the table2 keeps setting information which was valid to a given Stopdate. And I want to match now the items from table1 with the correct settings from table2. Therefore I have to do the date columns check


Can somebody please explain me how to deal with this?

Thanks in advance.
Re: Select Query with date comparison [message #270182 is a reply to message #270173] Wed, 26 September 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why you still don't want to follow the rules?

Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Select Query with date comparison [message #270185 is a reply to message #270173] Wed, 26 September 2007 01:22 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
My script:
CREATE TABLE table1 ( column1 VARCHAR2(10)
                    , column7 VARCHAR2(10)
                    , thedate DATE
                    )
/
CREATE TABLE table2 ( column1 VARCHAR2(10)
                    , column2 VARCHAR2(10)
                    , stopdate DATE
                    )
/

INSERT INTO table1 VALUES ('BMW', '3er', TO_DATE('02/24/2007','MM/DD/YYYY'));
INSERT INTO table1 VALUES ('BMW', '5er', TO_DATE('01/12/2004','MM/DD/YYYY'));

INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('01/12/2007','MM/DD/YYYY'));
INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('03/11/2007','MM/DD/YYYY'));
INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('07/12/2007','MM/DD/YYYY'));
INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('09/17/2007','MM/DD/YYYY'));

SELECT t1.column1
     , t1.column7
     , TO_CHAR(t1.thedate,'MM/DD/YYYY')       thedate
     , TO_CHAR(MIN(t2.stopdate),'MM/DD/YYYY') stopdate
FROM   table1 t1
   ,   table2 t2
WHERE  t1.column1 = t2.column1
AND    t1.column7 = t2.column2
AND    t1.thedate < t2.stopdate
GROUP  BY t1.column1
     , t1.column7
     , t1.thedate
/

DROP TABLE table1 PURGE
/
DROP TABLE table2 PURGE
/
Is this what you want? Next time, try to provide the creation script yourself. It makes life a lot easier for all of us. More tips? Click the link to the Forum Guide (in the yellow bar).

MHE
Re: Select Query with date comparison [message #270188 is a reply to message #270173] Wed, 26 September 2007 01:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Why so many underscores in your fields. Is it your data or what?
Re: Select Query with date comparison [message #270199 is a reply to message #270188] Wed, 26 September 2007 02:09 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The OP is using underscores as padding because they are unable/unwilling to use CODE tags to properly format their code.

Ironically, it will have taken far longer to format it with those tags thant it would have taken to click on the Code tag button.
Re: Select Query with date comparison [message #270260 is a reply to message #270173] Wed, 26 September 2007 06:13 Go to previous messageGo to next message
galaxy
Messages: 62
Registered: October 2005
Member
Sorry for the formating. Next time it will be correct!


But you query is not exactly what I want. I want as a result only one line for the line
'BMW', '3er', '02/24/2007'. So the result should be

'BMW', '3er', '02/24/2007','03/11/2007'

but your Query I think will deliver

'BMW', '3er', '02/24/2007','03/11/2007'
'BMW', '3er', '02/24/2007','07/12/2007'
'BMW', '3er', '02/24/2007','09/17/2007'

So I need only the line with the minimum Stopdate which is bigger than my Thedate Value.
Re: Select Query with date comparison [message #270261 is a reply to message #270260] Wed, 26 September 2007 06:15 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
galaxy wrote on Wed, 26 September 2007 13:13
but your Query I think will deliver
Let's find out then, shall we? Wink

SQL> SELECT banner FROM v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL>
SQL> CREATE TABLE table1 ( column1 VARCHAR2(10)
  2                      , column7 VARCHAR2(10)
  3                      , thedate DATE
  4                      )
  5  /

Table created.

SQL> CREATE TABLE table2 ( column1 VARCHAR2(10)
  2                      , column2 VARCHAR2(10)
  3                      , stopdate DATE
  4                      )
  5  /

Table created.

SQL>
SQL> INSERT INTO table1 VALUES ('BMW', '3er', TO_DATE('02/24/2007','MM/DD/YYYY')
);

1 row created.

SQL> INSERT INTO table1 VALUES ('BMW', '5er', TO_DATE('01/12/2004','MM/DD/YYYY')
);

1 row created.

SQL>
SQL> INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('01/12/2007','MM/DD/YYYY')
);

1 row created.

SQL> INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('03/11/2007','MM/DD/YYYY')
);

1 row created.

SQL> INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('07/12/2007','MM/DD/YYYY')
);

1 row created.

SQL> INSERT INTO table2 VALUES ('BMW', '3er', TO_DATE('09/17/2007','MM/DD/YYYY')
);

1 row created.

SQL>
SQL> SELECT t1.column1
  2       , t1.column7
  3       , TO_CHAR(t1.thedate,'MM/DD/YYYY')       thedate
  4       , TO_CHAR(MIN(t2.stopdate),'MM/DD/YYYY') stopdate
  5  FROM   table1 t1
  6     ,   table2 t2
  7  WHERE  t1.column1 = t2.column1
  8  AND    t1.column7 = t2.column2
  9  AND    t1.thedate < t2.stopdate
 10  GROUP  BY t1.column1
 11       , t1.column7
 12       , t1.thedate
 13  /

COLUMN1    COLUMN7    THEDATE    STOPDATE
---------- ---------- ---------- ----------
BMW        3er        02/24/2007 03/11/2007

SQL>
SQL> DROP TABLE table1 PURGE
  2  /

Table dropped.

SQL> DROP TABLE table2 PURGE
  2  /

Table dropped.
MHE
Re: Select Query with date comparison [message #270268 is a reply to message #270260] Wed, 26 September 2007 06:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Sorry for the formating. Next time it will be correct

Can we trust you as you have already been told that many times in your previous posts.

For myself, I'll start to answer to your question when YOU'll start to format it.
So maybe the next time.

Regards
Michel
Re: Select Query with date comparison [message #270870 is a reply to message #270173] Fri, 28 September 2007 03:58 Go to previous message
galaxy
Messages: 62
Registered: October 2005
Member
Hi,

I am really sorry but I have not seen the min() function.

I had exactly the same query (without the min()) and it did not work.

So thanks very much for this excellent help!
Previous Topic: sequence cretion
Next Topic: Change Precisssion
Goto Forum:
  


Current Time: Tue Dec 06 06:32:56 CST 2016

Total time taken to generate the page: 0.08419 seconds