Home » SQL & PL/SQL » SQL & PL/SQL » To take Previous date (Oracle 11g)
To take Previous date [message #637234] Wed, 13 May 2015 07:11 Go to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi,



i need to display the previous start date and end date if the date of main table is falling between start date and end date of ref table. for eg


create table ref_table(col_1 number,start_date date,end_date date);
create table main_table(date_1 date);

insert into ref_table
select 12,to_date(01/01/2015,DD-MON-YYYY),to_date(31/01/2015,DD-MON-YYYY) from dual;

insert into ref_table
select 12,to_date(01/02/2015,DD-MON-YYYY),to_date(28/02/2015,DD-MON-YYYY) from dual;

insert into ref_table
select 12,to_date(01/03/2015,DD-MON-YYYY),to_date(31/03/2015,DD-MON-YYYY) from dual;

insert into ref_table
select 12,to_date(01/04/2015,DD-MON-YYYY),to_date(30/04/2015,DD-MON-YYYY) from dual;

insert into main_table
select to_date(20/03/2015,DD-MON-YYYY) from dual;



ref_table
col_1	start_date	end_date
12	01/01/2015	31/01/2015
12	01/02/2015	28/02/2015
12	01/03/2015	31/03/2015
12	01/04/2015	30/04/2015

Main_table
date_1
20/03/2015

Output_table
col_1	start_date	end_date
12345678	01/02/2015	28/02/2015


date is falling between 01/03/201 and 31/03/2015 i need to take the previous start date and end date
01/02/2015,28/02/2015

Report message to a moderator

Re: To take Previous date [message #637235 is a reply to message #637234] Wed, 13 May 2015 07:13 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Your INSERT statements will not run.

Report message to a moderator

Re: To take Previous date [message #637236 is a reply to message #637235] Wed, 13 May 2015 07:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and once again the columns are not aligned and where does '12345678' come from?

Report message to a moderator

Re: To take Previous date [message #637239 is a reply to message #637236] Wed, 13 May 2015 07:35 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
 
create table ref_table(col_1 number,start_date date,end_date date);
create table main_table(date_1 date);

insert into ref_table
select 12,to_date('01-JAN-2015',DD-MON-YYYY),to_date('31-JAN-2015',DD-MON-YYYY) from dual;

insert into ref_table
select 12,to_date('01-JAN-2015',DD-MON-YYYY),to_date('28-FEB-2015',DD-MON-YYYY) from dual;

insert into ref_table
select 12,to_date('01-MAR-2015',DD-MON-YYYY),to_date('31-MAR-2015',DD-MON-YYYY) from dual;

insert into ref_table
select 12,to_date('01-APR-2015',DD-MON-YYYY),to_date('30-APR-2015',DD-MON-YYYY) from dual;

insert into main_table
select to_date('20-MAR-2015',DD-MON-YYYY) from dual;




ref_table
col_1	start_date	end_date
12	01/01/2015	31/01/2015
12	01/02/2015	28/02/2015
12	01/03/2015	31/03/2015
12	01/04/2015	30/04/2015

Main_table
date_1
20/03/2015

Output_table
col_1	start_date	end_date
12	01/02/2015	28/02/2015

Report message to a moderator

Re: To take Previous date [message #637243 is a reply to message #637239] Wed, 13 May 2015 07:45 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Think it through. First, select all rows from ref_table where end_date is less than the date in main_table. Write the query to do that. Then take that result set, and select the row with the highest end_date.

Report message to a moderator

Re: To take Previous date [message #637250 is a reply to message #637243] Wed, 13 May 2015 08:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and the test case still does not work. Please verify it BEFORE posting it.

Report message to a moderator

Re: To take Previous date [message #637254 is a reply to message #637250] Wed, 13 May 2015 09:20 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
 
create table ref_table(col_1 number,start_date date,end_date date);
create table main_table(date_1 date);

insert into ref_table
select 12,to_date('01-JAN-2015','DD-MON-YYYY'),to_date('31-JAN-2015','DD-MON-YYYY') from dual;

insert into ref_table
select 12,to_date('01-JAN-2015','DD-MON-YYYY'),to_date('28-FEB-2015','DD-MON-YYYY') from dual;

insert into ref_table
select 12,to_date('01-MAR-2015','DD-MON-YYYY'),to_date('31-MAR-2015','DD-MON-YYYY') from dual;

insert into ref_table
select 12,to_date('01-APR-2015','DD-MON-YYYY'),to_date('30-APR-2015','DD-MON-YYYY') from dual;

insert into main_table
select to_date('20-MAR-2015','DD-MON-YYYY') from dual;

[Updated on: Wed, 13 May 2015 09:44]

Report message to a moderator

Re: To take Previous date [message #637259 is a reply to message #637254] Wed, 13 May 2015 10:22 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
I have achived something like this but i want to insert into output_table where i have other select statement, i am not able to achive that

SELECT start_date,
  MAX(end_date)
FROM
  (SELECT *
  FROM ref_table1
  WHERE end_date <
    (SELECT date_1 FROM main_table WHERE rownum<2
    )
  )
GROUP BY start_date


insert into output_table
select 
col1, col2,(SELECT start_date,
  MAX(end_date)
FROM
  (SELECT *
  FROM ref_table1
  WHERE end_date <
    (SELECT date_1 FROM main_table WHERE rownum<2
    )
  )
GROUP BY start_date),
col3
from 
main_table
where
.
.

Report message to a moderator

Re: To take Previous date [message #637264 is a reply to message #637259] Wed, 13 May 2015 16:28 Go to previous message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
You would find life easier if you thought before writing. In your last post, you refer to ref_table1 when you mean ref_table. Well, OK, I can see what you mean. But then you refer to col_2 and col_3. What are they?

You will never make a successful career in IT if you do not pay attention to detail.

Report message to a moderator

Previous Topic: Transpose/Pivot with all combination
Next Topic: Query using Partitioning
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 14:56:32 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.