一条分两条

--一条拆成两条
--统计当前客户经理和客户关系，上年底客户经理和客户关系
--如果客户经理和客户关系跨越这两个时间段，那么一条分成两条
WITH
    basic_t AS (
          --客户和客户经理关系表
          SELECT 123 CUST_NO,'A' AM_NO, '20091231' start_dt , '29991231' end_dt FROM DUAL
          UNION ALL
          SELECT 456,'B', '20100401' start_dt , '29991231' end_dt  FROM DUAL
          UNION ALL
          SELECT 789,'C', '20100505' start_dt , '20110101' end_dt  FROM DUAL
    )
   
,   rela_peroid AS (
            --在统计日期和上年底的关系
            SELECT   t.cust_no
                   , t.am_no
                   , CASE WHEN      t.start_dt <= '20110425'
                               AND t.end_dt > '20110425'
                               AND t.start_dt <= SUBSTR('20110425',1,4)-1 || '1231'
                               AND t.end_dt > SUBSTR('20110425',1,4) -1 || '1231'
                               THEN 'ALL'
                          WHEN      t.start_dt <= '20110425'
                               AND  t.end_dt > '20110425'
                               THEN '20110425'
                          WHEN      t.start_dt <= SUBSTR('20110425',1,4)-1 || '1231'
                               AND t.end_dt > SUBSTR('20110425',1,4)-1 || '1231'
                               THEN SUBSTR('20110425',1,4)-1 ||'1231'
                    END stat_dt
            FROM basic_t t
            WHERE(
                       t.start_dt <= '20110425'  --当前
                   AND t.end_dt > '20110425'
                 )  
            OR  (
                       t.start_dt <= SUBSTR('20110425',1,4)-1 || '1231' --上年底
                   AND t.end_dt > SUBSTR('20110425',1,4)-1 || '1231'
                ) 
    )

--SELECT * FROM rela_peroid;
 
, one2two AS (
        SELECT 'ALL' stat_dt,decode(rownum,1,'20110425',substr('20110425',1,4) -1 || '1231') rp_dt
        from dual connect by rownum < 3;
)

SELECT   t.cust_no
       , t.am_no
       , DECODE(one2two.rp_dt,NULL,t.stat_dt,one2two.rp_dt) AS stat_dt
FROM rela_peroid t
left JOIN one2two
ON t.stat_dt = one2two.stat_dt
ORDER BY cust_no