Hi there,

I couldn't work out a SQL to archive this , can somebody help me out?

For the following records in source:
CUST_ID LE_ID_BUSN FMT_CD BUH_EFF_TS BUH_END_TS
--------------- ----------- --------------- -------------------------- --------------------------
6547 6547 lc 1965-12-31-00.00.00.000000 1966-01-01-00.00.00.000000
6547 6547 lc 1966-01-01-00.00.00.000000 2002-02-20-09.18.22.844000
6547 6547 lc 2002-02-20-09.18.22.844000 2003-09-11-10.47.01.022346
6547 6547 lc 2003-09-11-10.47.01.022346 2003-09-11-10.47.15.577973
6547 6547 pa 2003-09-11-10.47.15.577973 2006-09-15-08.14.03.490208
6547 6547 pa 2006-09-15-08.14.03.490208 2006-09-15-08.15.16.412741
6547 6547 pa 2006-09-15-08.15.16.412741 2006-09-15-08.16.10.878624
6547 6547 pa 2006-09-15-08.16.10.878624 2006-09-15-08.18.42.362458
6547 6547 pa 2006-09-15-08.18.42.362458 2006-09-15-08.18.45.443437
6547 6547 lc 2006-09-15-08.18.45.443437 -

also:
CUST_ID FMT_CD BUH_EFF_TS BUH_END_TS LE_ID_BUSN
--------------- --------------- -------------------------- -------------------------- -----------
100719 pa 1965-12-30-00.00.00.000000 1965-12-31-00.00.00.000000 100719
100719 pa 1965-12-31-00.00.00.000000 1965-12-31-00.00.00.000000 100719
100719 pa 1966-01-01-00.00.00.000000 2003-11-26-08.52.08.298775 100719
100719 a 2003-11-27-08.52.52.542204 2004-02-09-10.58.18.870532 100719
100719 a 2004-02-10-10.58.45.913380 - 100719


I want to create the target records by SQL:
cust_id le_busn_id fmt_cd startdate enddate
6547 6547 LC 1965-12-31 2003-09-11
6547 6547 PA 2003-09-11 2006-09-15
6547 6547 LC 2006-09-15 -
100719 100719 PA 1965-12-30 2003-11-26
100719 100719 A 2003-11-27 -

Thanks in advance.

If you had explained what you are trying to do with a sentence ot two, this would be easier. I think you want to list the continuous time periods for each CUST_ID, LE_ID_BUSN, and FMT_CD combination. If this is true, what you need is some recursive SQL. It would look something like:

with t1 (cust_id, le_id_busn,fmt_cd,start,end) as
(select cust_id,le_id_busn,fmt_cd,start,end from tab1 where (cust_id,le_id_busn,fmt_cd,start) not in (select cust_id,le_id_busn,fmt_cd,end from tab 1)
union all
select t1.cust_id,t1.le_id_busn,t1.fmt_cd,t1.start,tab1.e nd from t1, tab1 where (t1.cust_id = tab.cust_id) and (t1.le_id_busn = tab1.le_id_busn) and (t1.fmt_cd = tab1.fmt_cd) and (t1.end = tab1.start))
select cust_id,le_id_busn,fmt_cd,start,max(end) from t1 group by cust_id,le_id_busn,fmt_cd,start

This assumes your first table is "TAB1". You would need to do UNION ALL with the above to a similiar SQL for the second table.

HTH

Andy