Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query (Oracle 11g)
SQL Query [message #625074] Wed, 01 October 2014 03:05 Go to next message
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 Go to previous messageGo to next message
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 #625087 is a reply to message #625074] Wed, 01 October 2014 04:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Are rows in the table always matching your rules?

Re: SQL Query [message #625088 is a reply to message #625086] Wed, 01 October 2014 04:59 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thank you:)
Re: SQL Query [message #625089 is a reply to message #625088] Wed, 01 October 2014 05:01 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Yes, rows in the table always matching the rules
Re: SQL Query [message #625090 is a reply to message #625089] Wed, 01 October 2014 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So is this not just an ORDER BY DATE_FROM?

Re: SQL Query [message #625098 is a reply to message #625090] Wed, 01 October 2014 06:27 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
OH!! Yes... We can use just ORDER BY DATE_FROM

But It is just part of the whole requirement which I have got. I am not sure how I would have been implemented the same in larger picture.

No issues...the requirement has changed now and solution is less complicated now.

Thanks everybody...Smile
Re: SQL Query [message #625132 is a reply to message #625098] Wed, 01 October 2014 10:59 Go to previous message
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 Smile
Previous Topic: How to upload images into Oracle Table
Next Topic: table Data
Goto Forum:
  


Current Time: Fri Mar 29 06:31:55 CDT 2024