Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2006
    Posts
    33

    Question Unanswered: need help in SQL

    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.
    Last edited by jinsezh; 01-16-07 at 16:53.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •