| Oracle analytic query help [message #641114] |
Mon, 10 August 2015 13:15  |
parmarjm
Messages: 25
Registered: October 2006
|
Junior Member |
|
|
I am using Oracle 10g Release 10.0.2
Here is the situation:
I have record in my table looks like this :
Cust_id beg_dt end_dt sg_cd
264321502 01-MAY-97 19-MAR-98 1
264321502 21-MAY-98 15-OCT-98 6
264321502 20-OCT-98 22-APR-99 6
264321502 23-APR-99 25-APR-00 6
264321502 27-APR-00 20-JAN-02 6
264321502 25-JAN-02 15-MAY-02 6
264321502 21-MAY-02 27-MAY-02 6
264321502 31-MAY-02 17-FEB-03 6
264321502 21-FEB-03 06-SEP-04 1
264321502 25-FEB-03 25-FEB-03 1
264321502 31-MAR-03 30-APR-03 1
264321502 07-SEP-04 26-DEC-04 6
264321502 29-DEC-04 03-JAN-06 6
264321502 04-JAN-06 03-JAN-07 12
264321502 04-JAN-06 03-JAN-07 12
264321502 04-JAN-06 03-JAN-07 12
I need query output like
Cust_id beg_dt end_dt sg_cd
264321502 01-MAY-97 19-MAR-98 1
264321502 21-MAY-98 17-FEB-03 6
264321502 21-FEB-03 30-APR-03 1
264321502 07-SEP-04 03-JAN-06 6
264321502 04-JAN-06 03-JAN-07 12
basically, I need to pick min(beg_dt) max(end_dt) by sg_cd for this cust id.
Any help is very very appreciated:
My query is like this
select cust_id, beg_dt, end_dt, sg_cd,
min(beg_dt) over (partition by cust_id, sg_Cd) as new_beg_dt,
max(end_dt) over (partition by cust_id, sg_cd) as end_Dt_nw
from cust_tab;
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
