Home » SQL & PL/SQL » SQL & PL/SQL » SQL Question (Oracle 9i)
SQL Question [message #334953] Fri, 18 July 2008 17:39 Go to next message
jaingaurav2000
Messages: 4
Registered: July 2008
Junior Member
Hi All,

If the person and director is repeating for more than one consecutive records there I should get the minimum date of first record and maximum date of next record.

I am giving 2 possible scenario's below:

eg1:
Person start_date end_date Director
70 19-Feb-01 15-Sep-06 254
70 16-Sep-06 30-Nov-06 254
70 1-Dec-06 12-Aug-07 253
70 13-Aug-07 27-Sep-07 254
70 28-Sep-07 12-Dec-12 254

My output should look like

Person Start_date end_date Director
70 19-Feb-01 30-Nov-06 254
70 1-Dec-06 12-Aug-07 253
70 13-Aug-07 12-Dec-12 254

eg2:

Person start_date end_date Director
71 14-Feb-01 10-Sep-06 NULL
71 11-Sep-06 30-Nov-06 NULL
71 1-Dec-06 10-Aug-07 2001
71 11-Aug-07 27-Sep-07 NULL


My output should look like:

Person Start_date end_date Director
71 14-Feb-01 30-Nov-06 NULL
71 1-Dec-06 12-Aug-07 2001
71 11-Aug-07 28-Sep-07 NULL


Any help on this will be appreciated.

Thanks & Regards,
Gaurav






Re: SQL Question [message #334954 is a reply to message #334953] Fri, 18 July 2008 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

The collection of records in a table are like a bunch of colored balls in a basket.

Which is the 2nd red ball in the basket?

There is NO inherent order of rows in a table without the ORDER BY clause.
Re: SQL Question [message #334956 is a reply to message #334954] Fri, 18 July 2008 18:03 Go to previous messageGo to next message
jaingaurav2000
Messages: 4
Registered: July 2008
Junior Member
I didn't get your response. Do you mean that it cannot be done. Kindly elaborate.

Thanks for you time.
Re: SQL Question [message #334957 is a reply to message #334956] Fri, 18 July 2008 18:11 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Your question makes no sense. Define what makes a record first and second and third et al.
Re: SQL Question [message #334958 is a reply to message #334957] Fri, 18 July 2008 18:17 Go to previous messageGo to next message
jaingaurav2000
Messages: 4
Registered: July 2008
Junior Member


If a person & director record is repeating multiple number of times one after the another then I want to take minimum date of the first record and maximum date of the last record.

What I mean is, all the consecutive records of a unique combination of person & director should be collapsed into one record.

Hope its clear

Re: SQL Question [message #334959 is a reply to message #334953] Fri, 18 July 2008 18:29 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
What do you want to happen when the 1st row returned is:
70 28-Sep-07 12-Dec-12 254
and the next row returned is:
70 13-Aug-07 27-Sep-07 254
Re: SQL Question [message #334960 is a reply to message #334959] Fri, 18 July 2008 18:40 Go to previous messageGo to next message
jaingaurav2000
Messages: 4
Registered: July 2008
Junior Member
In the example below

eg1:
Person start_date end_date Director
70 19-Feb-01 15-Sep-06 254
70 16-Sep-06 30-Nov-06 254
70 1-Dec-06 12-Aug-07 253
70 13-Aug-07 27-Sep-07 254
70 28-Sep-07 12-Dec-12 254


For person = 70 Director = 254 is repeating from 19-Feb-01 to 30-Nov-06 then 253 was director from 1-Dec-06 to 12-Aug-07 and then again 254 was director from 13-Aug-07 to 12-Dec-12. I want 3 records only as output instead of above 5 records


My output should look like

Person Start_date end_date Director
70 19-Feb-01 30-Nov-06 254
70 1-Dec-06 12-Aug-07 253
70 13-Aug-07 12-Dec-12 254


Re: SQL Question [message #334976 is a reply to message #334953] Sat, 19 July 2008 00:58 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.
(Remember that a string is NOT a date, use TO_DATE function, and that some do not use english month name.)

Clue: Have a look at LAG/LEAD functions.

Regards
Michel

[Updated on: Sat, 19 July 2008 01:00]

Report message to a moderator

Previous Topic: Using DECODE to determine if a date falls within a date range
Next Topic: How to see the structure of view on command line
Goto Forum:
  


Current Time: Fri Dec 02 12:22:04 CST 2016

Total time taken to generate the page: 0.13272 seconds