SQL Query [message #625074] |
Wed, 01 October 2014 03:05 |
srinivas.k2005
Messages: 404 Registered: August 2006
|
Senior Member |
|
|
Hi,
Create table Subscriber
(
date_from DATE,
date_to DATE,
ID VARCHAR2(20)
);
Insert into Subscriber values ( TO_DATE('2013/05/03', 'yyyy/mm/dd'), TO_DATE('2014/05/03', 'yyyy/mm/dd'),'RSPACK');
Insert into Subscriber values ( TO_DATE('2003/05/03', 'yyyy/mm/dd'), TO_DATE('2004/05/03', 'yyyy/mm/dd'),'AAA');
Insert into Subscriber values ( TO_DATE('2007/05/03', 'yyyy/mm/dd'), TO_DATE('2008/05/03', 'yyyy/mm/dd'),'AAA');
Insert into Subscriber values ( TO_DATE('2009/05/03', 'yyyy/mm/dd'), TO_DATE('2010/05/03', 'yyyy/mm/dd'),'RSPACK');
Insert into Subscriber values ( TO_DATE('2005/05/03', 'yyyy/mm/dd'), TO_DATE('2006/05/03', 'yyyy/mm/dd'),'RSPACK');
Insert into Subscriber values ( TO_DATE('2011/05/03', 'yyyy/mm/dd'), TO_DATE('2012/05/03', 'yyyy/mm/dd'),'AAA');
Expected output result:
DATE_FROM DATE_TO ID
----------- ----------- --------------------
03-05-2003 03-05-2004 AAA
03-05-2005 03-05-2006 RSPACK
03-05-2007 03-05-2008 AAA
03-05-2009 03-05-2010 RSPACK
03-05-2011 03-05-2012 AAA
03-05-2013 03-05-2014 RSPACK
Requirement details:
Here ID's AAA and RSPACK should exist in pairs ONLY like,
AAA
RSPACK
AAA
RSPACK
....
Also,Date value for DATE_TO from ID AAA should be on or before date value for DATE_FROM from RSPACK like below,
DATE_FROM DATE_TO ID
---------- ------- -----
03-05-2003 03-05-2004 AAA
03-05-2005 03-05-2006 RSPACK
In the next set, Date value for DATE_TO from ID RSPACK should be on or before date value for DATE_FROM from AAA like below,
DATE_FROM DATE_TO ID
--------- -------- ---
03-05-2005 03-05-2006 RSPACK
03-05-2007 03-05-2008 AAA
Similarly next set, Date value for DATE_TO from ID AAA should be on or before date value for DATE_FROM from RSPACK like below,
DATE_FROM DATE_TO ID
--------- ------- ------
03-05-2007 03-05-2008 AAA
03-05-2009 03-05-2010 RSPACK
..........
.........
......Till the end of the records in the table.
Please advise. Any idea please.
Regards,
SRK
Edited by Lalit : Removed superfluous lines at the end
[Updated on: Wed, 01 October 2014 04:57] by Moderator Report message to a moderator
|
|
|
Re: SQL Query [message #625086 is a reply to message #625074] |
Wed, 01 October 2014 04:54 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
srinivas.k2005 wrote on Wed, 01 October 2014 13:35
Expected output result:
DATE_FROM DATE_TO ID
----------- ----------- --------------------
03-05-2003 03-05-2004 AAA
03-05-2005 03-05-2006 RSPACK
03-05-2007 03-05-2008 AAA
03-05-2009 03-05-2010 RSPACK
03-05-2011 03-05-2012 AAA
03-05-2013 03-05-2014 RSPACK
I don't see anything other than ORDER BY the two dates, first on "DATE_FROM", then on "DATE_TO" :
SQL> alter session set nls_date_format='DD-MM-YYYY';
Session altered.
SQL> select * from Subscriber order by 1,2;
DATE_FROM DATE_TO ID
---------- ---------- --------------------
03-05-2003 03-05-2004 AAA
03-05-2005 03-05-2006 RSPACK
03-05-2007 03-05-2008 AAA
03-05-2009 03-05-2010 RSPACK
03-05-2011 03-05-2012 AAA
03-05-2013 03-05-2014 RSPACK
6 rows selected.
Edit : Altered the session to set NLS_DATE_FORMAT
[Updated on: Wed, 01 October 2014 04:56] Report message to a moderator
|
|
|
|
|
|
|
|
Re: SQL Query [message #625132 is a reply to message #625098] |
Wed, 01 October 2014 10:59 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Probably now you understand why we first ask the posters to show what they have already tried. The time and effort you spent to post the question was not actually worth the short and simple answer
|
|
|