-- 테스트 데이터...

WITH
  t AS
   (   
 SELECT 111 AS 코드, '2007-07-01' AS 등록일자, '2007-07-01' AS 일자1, '2007-07-10' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-02' AS 등록일자, '2007-07-02' AS 일자1, '2007-07-11' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-03' AS 등록일자, '2007-07-03' AS 일자1, '2007-07-13' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-04' AS 등록일자, '2007-07-04' AS 일자1, '2007-07-14' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-06' AS 등록일자, '2007-07-07' AS 일자1, '2007-07-17' AS 일자2,  90 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-07' AS 등록일자, '2007-07-07' AS 일자1, '2007-07-17' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-08' AS 등록일자, '2007-07-08' AS 일자1, '2007-07-18' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-09' AS 등록일자, '2007-07-09' AS 일자1, '2007-07-19' AS 일자2, 100 AS 수량 FROM dual UNION ALL
    SELECT 111 AS 코드, '2007-07-10' AS 등록일자, '2007-07-10' AS 일자1, '2007-07-20' AS 일자2, 100 AS 수량 FROM dual
 )

-- 쿼리...;

SELECT
      코드
    , MIN(DECODE(gb2, 1, 등록일자)) AS 최근등록일자
    , MIN(DECODE(gb2, 1, 일자1)) AS 최근일자1
    , MIN(DECODE(gb2, 1, 일자2)) AS 최근일자2
    , MIN(DECODE(gb2, 1, 수량)) AS 최근수량
    , MIN(DECODE(gb1, 1, 등록일자)) AS 중도등록일자
    , MIN(DECODE(gb1, 1, 일자1)) AS 중도일자1
    , MIN(DECODE(gb1, 1, 일자2)) AS 중도일자2
    , MIN(DECODE(gb1, 1, 수량)) AS 중도수량
FROM(
    SELECT
          t.*
        , CASE  WHEN  LAG(TO_DATE(등록일자, 'YYYY-MM-DD'), 1, TO_DATE(등록일자, 'YYYY-MM-DD') - 1) OVER(PARTITION BY 코드 ORDER BY 등록일자)
                    = TO_DATE(등록일자, 'YYYY-MM-DD') - 1 THEN 0
                ELSE 1
          END AS gb1
        , CASE WHEN ROW_NUMBER() OVER(PARTITION BY 코드 ORDER BY 등록일자) = COUNT(*) OVER() THEN 1
          END AS gb2
    FROM t
    ) tt
WHERE gb1 = 1 OR gb2 = 1
GROUP BY 코드

건승하시길...수고하세요~~