Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

summarize records by date range

tfaJun 11 2010 — edited Nov 26 2010
I have a challenging project where I need to report a summary record for a range of dates for each ID. I'm not against using a plsql helper function to derive the summary result as I'm not sure it can all be done in sql. Any help would be appreciated as my attempts have failed.

Here is a sample of the data.

ID START_DATE END_DATE
445 01-JAN-2010 30-APR-2010 --example concurrent
445 01-JAN-2010 31-MAY-2010
445 17-MAY-2010 06-AUG-2010
2710 01-MAY-2010 31-AUG-2010 --example consecutive
2710 01-SEP-2010 31-DEC-2010
2710 01-JAN-2011 30-APR-2011
2710 01-MAY-2011 31-AUG-2011
658 01-JAN-2010 30-APR-2010 --example concurrent
658 01-JAN-2010 31-MAY-2010
658 01-JAN-2010 31-MAY-2010
108 28-DEC-2009 22-JAN-2010 --example non-consecutive
108 29-MAR-2010 11-APR-2010
108 01-MAY-2010 31-MAY-2010
2535 01-MAR-2010 14-MAR-2010 --example 2 consecutive and 1 non-consecutive combination
2535 15-MAR-2010 28-MAR-2010
2535 05-APR-2010 02-MAY-2010
999 01-MAR-2010 14-MAR-2010 --example 2 concurrent and 1 consecutive combination
999 01-MAR-2010 24-APR-2010
999 25-APR-2010 02-MAY-2010


Here is the summary result of what I would like returned for each ID

ID START_DATE END_DATE
445 01-JAN-2010 06-AUG-2010
2710 01-MAY-2010 31-AUG-2011
658 01-JAN-2010 31-MAY-2010
108 01-MAY-2010 31-MAY-2010
2535 05-APR-2010 02-MAY-2010
999 01-MAR-2010 02-MAY-2010

Thanks,
Todd
This post has been answered by Frank Kulash on Jun 11 2010
Jump to Answer

Comments

Hello,


It's a curious question.

To access to My Oracle Support (ex Metalink) you need a CSI (Customer Support Identifier) which is
given by Oracle if you get a Licence (or partner) contract with Oracle.

So, for all I know, it's not free.

However, you can ask, for instance, for Oracle Database Personal Edition and for 1 or 2 Years Term Licence.

Normally, the 1 Year Term Licence cost about 20% of the Perpetual Licence.

To check these informatiions (which can be changed any time) you may consult the following link:

http://www.oracle.com/corporate/pricing/pricelists.html

Else, if you are in a firm which already has a CSI you may ask to the DBA (or manager) who manage this
CSI account so as to give you acces to Metalink. In this case there's nothing more to pay.


Hope this help.
Best regards,
Jean-Valentin
Aman....
Tania,

MOS account is not for free but yes you can opt to but you may choose to buy personal edition of Oracle and with that, you may want to opt for the MOS account as well.

There was a thread where Hans did mention how to go for it. I am not able to find it somehow at the moment. But still there is one thread which does have the same discussion going on that how one can go for personal MOS account.
1039370

HTH
Aman....
1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 12 2010
Added on Jun 11 2010
20 comments
12,839 views