| SQL Question [message #334953] |
Fri, 18 July 2008 17:39  |
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 #334958 is a reply to message #334957] |
Fri, 18 July 2008 18:17  |
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 #334960 is a reply to message #334959] |
Fri, 18 July 2008 18:40 |
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 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
