Home » SQL & PL/SQL » SQL & PL/SQL » to derive previous dates and ignore first date (Oracle 11g)
to derive previous dates and ignore first date [message #641438] Fri, 14 August 2015 15:35 Go to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi,

I am having below two tables

  periods

START_DATE	END_DATE
30-SEP-2005 00.00.00	13-OCT-2005 00.00.00
14-OCT-2005 00.00.00	14-NOV-2005 00.00.00
15-NOV-2005 00.00.00	14-DEC-2005 00.00.00
 
 
ref_table
REF_DATE
07-NOV-2005 00.00.00
07-DEC-2005 00.00.00
06-JAN-2006 00.00.00


i need to check ref_date i.e 07-NOV-2005 falling between start and end dates of periods table i.e 14-oct-2005 and 14-Nov-2005(second record) hence derive the previous dates. like this we need to check but the problem here it would for 06-JAN which will not fall in any dates but need to consider the last dates, also to ignore first period dates

output
START_DATE END_DATE  ref_DATE 
14-OCT-05 14-NOV-05 07-DEC-05 
15-NOV-05 14-DEC-05 06-JAN-06



Insert into PERIODS (START_DATE,END_DATE) values (to_date('30-SEP-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'),to_date('13-OCT-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into PERIODS(START_DATE,END_DATE) values (to_date('14-OCT-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'),to_date('14-NOV-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into PERIODS(START_DATE,END_DATE) values (to_date('15-NOV-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'),to_date('14-DEC-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
 
Insert into REF_TABLE (REF_DATE) values (to_date('07-NOV-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into REF_TABLE (REF_DATE) values (to_date('07-DEC-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into REF_TABLE (REF_DATE) values (to_date('06-JAN-2006 00.00.00','DD-MON-YYYY HH24.MI.SS'));

CREATE TABLE "PERIODS"
  (
  "START_DATE" DATE,
  "END_DATE"   DATE
  );
 
 
CREATE TABLE "REF_TABLE"
  (
  "REF_DATE" DATE
  );


Report message to a moderator

Re: to derive previous dates and ignore first date [message #641450 is a reply to message #641438] Sat, 15 August 2015 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand why 07-NOV-2005 which is your example to explain your "specification" is not in the result, otherwise one solution to the sentence but "also to ignore first period dates" which is not understandable, is:
SQL> with maxint as (select max(start_date) max_start, max(end_date) max_end from periods)
  2  select nvl(p.start_date, m.max_start) start_date,
  3         nvl(p.end_date, m.max_end) end_date,
  4         r.ref_date
  5  from ref_table r
  6       left outer join periods p
  7         on p.start_date <= r.ref_date and p.end_date >= r.ref_date
  8       left outer join maxint m
  9         on m.max_end < r.ref_date
 10  order by r.ref_date
 11  /

START_DATE  END_DATE    REF_DATE
----------- ----------- -----------
14-OCT-2005 14-NOV-2005 07-NOV-2005
15-NOV-2005 14-DEC-2005 07-DEC-2005
15-NOV-2005 14-DEC-2005 06-JAN-2006

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641453 is a reply to message #641450] Sat, 15 August 2015 03:44 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi,

I would like the output like below where in first i need to check if ref_date i.e for eg 07-NOV-2005 is falling between 14-OCT-2005 14-NOV-2005 of periods date and need to consider the previous start dates hence it would be

Start_date end_Date ref_Date
30-SEP-2005 13-OCT-2005 07-NOV-2005

like wise i need to derive the dates but since 06-JAN-2006 will not fall in any dates i need to map that dates like below

START_DATE END_DATE ref_DATE
30-SEP-2005 13-OCT-2005 07-NOV-2005
14-OCT-05 14-NOV-05 07-DEC-05
15-NOV-05 14-DEC-05 06-JAN-06

once the above output is derived i need to ignore the first periods 
START_DATE END_DATE  ref_DATE 
14-OCT-05 14-NOV-05 07-DEC-05 
15-NOV-05 14-DEC-05 06-JAN-06

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641454 is a reply to message #641453] Sat, 15 August 2015 04:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is this not what I posted (just remove the first row if you don't want it)?

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641455 is a reply to message #641454] Sat, 15 August 2015 05:01 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
But second record of your output doesnt matches my output and can you let me know how i can ignore the first periods

START_DATE  END_DATE    REF_DATE
----------- ----------- -----------
14-OCT-2005 14-NOV-2005 07-NOV-2005
15-NOV-2005 14-DEC-2005 07-DEC-2005
15-NOV-2005 14-DEC-2005 06-JAN-2006

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641456 is a reply to message #641455] Sat, 15 August 2015 05:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
But second record of your output doesnt matches my output an


So you badly explained your requirements.
First details them WITHOUT referring to the example, just with words.

Then explain each column of each line of your result.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641457 is a reply to message #641456] Sat, 15 August 2015 07:16 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Ok let me explain you


Start date End date derived_Ref_Date comments
30-Sep-05 13-Oct-05 07-Nov-05 compare 07th nov which falls between start and end dates of periods
table which falls between 14-oct-05
and 14-Nov-05
and map with previous date

14-Oct-05 14-Nov-05 07-Dec-05 compare 07-DEC-2005 which falls between 15-Nov-05
and
14-Dec-05 and map with previous start dates
15-Nov-05 14-Dec-05 06-Jan-06 06-JAN-06 which doesn't falls in any dates needs
to mapped in last periods

After deriving the above then ignore first record

output

START_DATE END_DATE ref_DATE
14-OCT-05 14-NOV-05 07-DEC-05
15-NOV-05 14-DEC-05 06-JAN-06

attached txt file in better format

[Updated on: Sat, 15 August 2015 07:19]

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641458 is a reply to message #641457] Sat, 15 August 2015 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
30-Sep-05 13-Oct-05 07-Nov-05 compare 07th nov which falls between start and end dates of periods
table which falls between 14-oct-05
and 14-Nov-05
and map with previous date


What previous dates? what previous refer to? What dates refer to?
For me there is no previous date as this is the first row!
First sentence, I don't understand, I stop.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641459 is a reply to message #641458] Sat, 15 August 2015 08:19 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
what i mean to say is ref_Date to compare between start and end dates from periods and derive previous dates i.e 07-NOV-2005 from ref_table falls between 14-OCT-2005 00.00.00 14-NOV-2005 from periods table and derive previous dates like below transformed output

start_DAte end_Date ref_Date
30-SEP-2005 13-OCT-2005 07-NOV-2005

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641461 is a reply to message #641459] Sat, 15 August 2015 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
what i mean to say is ref_Date to compare between start and end dates from periods and derive previous dates


You repeat but do not explain anything.
What is the definition of "previous dates"?

Quote:
start_DAte end_Date ref_Date
30-SEP-2005 13-OCT-2005 07-NOV-2005


You repeat this all the time, it is not in your result.

Why this one? Where does 30-SEP-2005 come from? Where does come from?

Do NOT refer to the example, tell us WITHOUT REFERRING TO THE EXAMPLE, what result you want.
Say I have 2 tables like yours, I have my data, what should be the result I have to give you to fulfill the requirements?
Say I have 5 periods A, B, C, D, E and 3 dates X, Y, Z what should be the result for Y?

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641462 is a reply to message #641461] Sat, 15 August 2015 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or do you want the latest period before the each ref_date?
SQL> select max(start_date) start_date, max(end_date) end_date, ref_date
  2  from ref_table, periods
  3  where end_date < ref_date
  4  group by ref_date
  5  order by ref_date
  6  /
START_DATE  END_DATE    REF_DATE
----------- ----------- -----------
30-SEP-2005 13-OCT-2005 07-NOV-2005
14-OCT-2005 14-NOV-2005 07-DEC-2005
15-NOV-2005 14-DEC-2005 06-JAN-2006

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641471 is a reply to message #641462] Sun, 16 August 2015 07:27 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi, Yeah the above one is what i required is it possible to ignore the first periods i.e first record in your query
SQL> select max(start_date) start_date, max(end_date) end_date, ref_date
  2  from ref_table, periods
  3  where end_date < ref_date
  4  group by ref_date
  5  order by ref_date
  6  /
 which will result in below output

14-OCT-2005 14-NOV-2005 07-DEC-2005
15-NOV-2005 14-DEC-2005 06-JAN-2006

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641472 is a reply to message #641471] Sun, 16 August 2015 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Is "latest period before the each ref_date" the correct specification? Do not refer to the result, is it this?

Yes, you can remove it, just give a number at each returned row and do not select the number 1.

Note my query make some assumptions on the data.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641473 is a reply to message #641472] Sun, 16 August 2015 08:27 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
yes latest period before each ref_Date is correct specification. i required that only. in your query i need to ignore the first record after deriving the out. can you help me in that with your query or any other way

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641474 is a reply to message #641473] Sun, 16 August 2015 10:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Michel Cadot wrote on Sun, 16 August 2015 14:52
...
Yes, you can remove it, just give a number at each returned row and do not select the number 1.
...

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641482 is a reply to message #641474] Sun, 16 August 2015 15:03 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Thanks i am having one more table where in i need to check derived max(start_date) start_date, max(end_date) end_date from your query to compare whether they are falling between dates of that table

for eg


Master_table
Start_Date End_Date
01-SEP-2005  01-DEC-2005

insert into master_table
select to_Date('01-SEP-2005','DD-MON-YYYY'),to_Date('01-DEC-2005','DD-MON-YYYY') from dual;

create table master_table(start_Date date,end_Date date);


i need to compare the derived dates for e.g. 14-OCT-2005 14-NOV-2005 which falls between start_Date and end_Date of master table hence my output will be like below


START_DATE  END_DATE    REF_DATE
----------- ----------- -----------
30-SEP-2005 13-OCT-2005 07-NOV-2005  - ignoring first record
14-OCT-2005 14-NOV-2005 07-DEC-2005 - this falls between dates of master_table
15-NOV-2005 14-DEC-2005 06-JAN-2006 - this will be ignored because not falling between the dates of master_table i mean partial dates which needs to be ignored

final output 
START_DATE  END_DATE    REF_DATE
14-OCT-2005 14-NOV-2005 07-DEC-2005

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641487 is a reply to message #641482] Mon, 17 August 2015 00:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Combine my first and last queries.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641504 is a reply to message #641487] Mon, 17 August 2015 03:45 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
hi is it something like this , but i am not able to get the query 

 select max(start_date) start_date, max(end_date) end_date, ref_date
 from ref_table, periods,(select start_date,end_date from master_table) ms
 where end_date < ref_date
and start_date between ms.start_date and ms.end_date
 group by ref_date
 order by ref_date

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641505 is a reply to message #641504] Mon, 17 August 2015 03:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Does this give the correct result?

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641526 is a reply to message #641438] Mon, 17 August 2015 09:30 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Nope but i am struck on this

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641529 is a reply to message #641526] Mon, 17 August 2015 09:46 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
It helps if you tell what result it is giving and how it's wrong.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641535 is a reply to message #641529] Mon, 17 August 2015 10:23 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
getting below error
SELECT MAX(START_DATE) START_DATE,
	MAX(END_DATE) END_DATE,
	REF_DATE
FROM REF_TABLE re,
	PERIODS,
	(SELECT M.START_DATE,
		m.END_DATE
	FROM MASTER_TABLE m
	) MS
WHERE END_DATE < REF_DATE
AND re.START_DATE BETWEEN MS.START_DATE AND MS.END_DATE
GROUP BY REF_DATE
ORDER BY REF_DATE;

ORA-00904: "RE"."START_DATE": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:
Error at Line: 62 Column: 5

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641536 is a reply to message #641535] Mon, 17 August 2015 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
What columns are in ref_table?

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641537 is a reply to message #641535] Mon, 17 August 2015 10:40 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Quote:
Insert into PERIODS (START_DATE,END_DATE) values (to_date('30-SEP-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'),to_date('13-OCT-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into PERIODS(START_DATE,END_DATE) values (to_date('14-OCT-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'),to_date('14-NOV-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into PERIODS(START_DATE,END_DATE) values (to_date('15-NOV-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'),to_date('14-DEC-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
 
Insert into REF_TABLE (REF_DATE) values (to_date('07-NOV-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into REF_TABLE (REF_DATE) values (to_date('07-DEC-2005 00.00.00','DD-MON-YYYY HH24.MI.SS'));
Insert into REF_TABLE (REF_DATE) values (to_date('06-JAN-2006 00.00.00','DD-MON-YYYY HH24.MI.SS'));

CREATE TABLE "PERIODS"
  (
  "START_DATE" DATE,
  "END_DATE"   DATE
  );
 
 
CREATE TABLE "REF_TABLE"
  (
  "REF_DATE" DATE
  );

I don't see, in your table definition, how you are relating a record in your REF_TABLE to a record in your PERIODS table. There does not appear to be any Foreign Key defined. Without this, how do you expect to uniquely tie a REF_DATE record to a PERIODS record?

Craig...

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641538 is a reply to message #641537] Mon, 17 August 2015 10:43 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ref_date is supposed to fall between a start_date and end_date on periods.
There's no fk you can create for that.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641542 is a reply to message #641538] Mon, 17 August 2015 11:10 Go to previous messageGo to next message
CraigB
Messages: 386
Registered: August 2014
Location: Utah, USA
Senior Member
Based on the expected output, it appeared that the OP was performing a row-by-row comparison of the REF_DATE to a row-by-row comparison in the PERIODS table. This is what was confusing. So, the output that really is expected is "Show only rows in the PERIODS table where there is a REF_DATE that falls between those two dates BUT for REF_TABLE dates that do not have a matching "Between" row in PERIODS table, show the MAX(START_DT) and MAX(END_DT) row from the PERIODS table.

Quote:
what i mean to say is ref_Date to compare between start and end dates from periods and derive previous dates i.e 07-NOV-2005 from ref_table falls between 14-OCT-2005 00.00.00 14-NOV-2005 from periods table and derive previous dates like below transformed output

start_DAte end_Date ref_Date
30-SEP-2005 13-OCT-2005 07-NOV-2005

This statement is part of what made me think there was a relationship between the rows in the REF_TABLE and the rows in the PERIODS table. I think I am just confusing the issue even further so I will gracefully bow out of this thread...

Craig...

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641543 is a reply to message #641542] Mon, 17 August 2015 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said you just have to mix both my queries:
SQL> select max(start_date) start_date, max(end_date) end_date, ref_date
  2  from ref_table r, periods p1
  3  where end_date < ref_date
  4    and exists (select null from periods p2
  5                where p2.start_date <= r.ref_date and p2.end_date >= r.ref_date)
  6  group by ref_date
  7  order by ref_date
  8  /
START_DATE  END_DATE    REF_DATE
----------- ----------- -----------
30-SEP-2005 13-OCT-2005 07-NOV-2005
14-OCT-2005 14-NOV-2005 07-DEC-2005

(and remove the first one which is not wanted.)

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641544 is a reply to message #641542] Mon, 17 August 2015 11:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Based on the expected output, it appeared that the OP was performing a row-by-row comparison of the REF_DATE to a row-by-row comparison in the PERIODS table. This is what was confusing.


This is correct. OP is unable to give a SQL or relational specification, it can only give some sort of algorithm.
The first specification was "latest period before each ref_date".
The last one was "latest period before each ref_date only if ref_date falls into a known period".
With the exception to the rule that the first one should be omitted.
Of course, many things are missing in this "specification" like "what happens if a ref_date falls into a known period and there is no previous period?" or "can the periods overlap?"...

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641806 is a reply to message #641543] Mon, 24 August 2015 16:44 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi,

I need to refer master_table where i am not seeing your table i mean i need to check the derived max(start_date) start_date, max(end_date) end_date value and compare these dates whether falling between start and end dates of master_table

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641807 is a reply to message #641806] Mon, 24 August 2015 18:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rohit_shinez wrote on Mon, 24 August 2015 14:44
Hi,

I need to refer master_table where i am not seeing your table i mean i need to check the derived max(start_date) start_date, max(end_date) end_date value and compare these dates whether falling between start and end dates of master_table


since you can't or won't clearly define the requirements, no one can produce any code.

How will I know when correct solution has been posted here?

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641815 is a reply to message #641806] Tue, 25 August 2015 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

rohit_shinez wrote on Mon, 24 August 2015 23:44
Hi,

I need to refer master_table where i am not seeing your table i mean i need to check the derived max(start_date) start_date, max(end_date) end_date value and compare these dates whether falling between start and end dates of master_table


This is completely meaningless for me.
How this sentence is related to your original question?
How my previous query does not fit your needs?

[Updated on: Tue, 25 August 2015 00:11]

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641887 is a reply to message #641815] Wed, 26 August 2015 16:49 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Hi,

what i am trying to say here is i need to compare the derived max and min dates as per your query result which is absolutely fine, i need to check whether the derived dates i.e., max(start_date) start_date, max(end_date) end_date value to check if they fall between the start and end dates of another table called master_table

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641888 is a reply to message #641887] Wed, 26 August 2015 17:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You're On Your Own (YOYO) unless & until you do as below

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641891 is a reply to message #641887] Wed, 26 August 2015 23:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rohit_shinez wrote on Wed, 26 August 2015 23:49
Hi,

what i am trying to say here is i need to compare the derived max and min dates as per your query result which is absolutely fine, i need to check whether the derived dates i.e., max(start_date) start_date, max(end_date) end_date value to check if they fall between the start and end dates of another table called master_table


So do it.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641912 is a reply to message #641891] Thu, 27 August 2015 08:25 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
I tried but its returning all data
SELECT start_date,
  end_date,
  ref_date
FROM
  (SELECT MAX(start_date) start_date,
    MAX(end_date) end_date,
    ref_date
  FROM ref_table r,
    periods p1
  WHERE end_date < ref_date
  GROUP BY ref_date
  )
  where exists(select 1 from master_table ms where start_date between ms.start_date and ms.end_date);

START_DATE	END_DATE	REF_DATE
30-SEP-05 00.00.00	13-OCT-05 00.00.00	07-NOV-05 00.00.00
14-OCT-05 00.00.00	14-NOV-05 00.00.00	07-DEC-05 00.00.00
15-NOV-05 00.00.00	14-DEC-05 00.00.00	06-JAN-06 00.00.00


Report message to a moderator

Re: to derive previous dates and ignore first date [message #641914 is a reply to message #641912] Thu, 27 August 2015 08:50 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well that's because this:
where start_date between ms.start_date and ms.end_date

Is the same as this:
where 1=1

master_table.start_date must always be between master_table.start_Date and anything else.
If you meant to use start_date from a different table you need to use a table name or alias to tell oracle that.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641915 is a reply to message #641914] Thu, 27 August 2015 08:54 Go to previous messageGo to next message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Yeah but how can i do that

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641916 is a reply to message #641915] Thu, 27 August 2015 08:56 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
rohit_shinez wrote on Thu, 27 August 2015 06:54
Yeah but how can i do that

Learn to write SQL to match specifications, instead of random collection of characters which produce valid but meaningless syntax.

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641917 is a reply to message #641915] Thu, 27 August 2015 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
Senior Member
rohit_shinez wrote on Thu, 27 August 2015 14:54
Yeah but how can i do that

?
The query already contains some table alises, are you really saying you can't work out how to add one?

Report message to a moderator

Re: to derive previous dates and ignore first date [message #641919 is a reply to message #641917] Thu, 27 August 2015 09:14 Go to previous messageGo to previous message
rohit_shinez
Messages: 139
Registered: January 2015
Senior Member
Alias is there but not getting the required value:
START_DATE  END_DATE    REF_DATE
----------- ----------- -----------
30-SEP-2005 13-OCT-2005 07-NOV-2005  - ignoring first record
14-OCT-2005 14-NOV-2005 07-DEC-2005 - this falls between dates of master_table
15-NOV-2005 14-DEC-2005 06-JAN-2006 - this will be ignored because not falling between the dates of master_table i mean partial dates which needs to be ignored

final output 
START_DATE  END_DATE    REF_DATE
14-OCT-2005 14-NOV-2005 07-DEC-2005

Report message to a moderator

Previous Topic: Constraint problem for flag
Next Topic: Table Lock
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Apr 24 08:10:30 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.