연승구하기랑 비슷해보여서 링크 달아봅니다.

http://www.sqler.com/608815

간단할줄 알았는데....의외로 복잡해지네요.. 누적함수가 있는 버전이면 좀 수월할듯...

WITH TEST AS

(

SELECT '2015-01-01' DT, 1 SEQ, 'AAA' PARTNO, 'N' GUBUN UNION ALL

SELECT '2015-01-01' DT, 1 SEQ, 'BBB' PARTNO, 'N' GUBUN UNION ALL

SELECT '2015-01-01' DT, 2 SEQ, 'BBB' PARTNO, 'Y' GUBUN UNION ALL

SELECT '2015-01-01' DT, 1 SEQ, 'CCC' PARTNO, 'N' GUBUN UNION ALL

SELECT '2015-01-02' DT, 1 SEQ, 'AAA' PARTNO, 'Y' GUBUN UNION ALL

SELECT '2015-01-02' DT, 1 SEQ, 'DDD' PARTNO, 'N' GUBUN UNION ALL

SELECT '2015-01-02' DT, 1 SEQ, 'BBB' PARTNO, 'Y' GUBUN UNION ALL

SELECT '2015-01-02' DT, 1 SEQ, 'CCC' PARTNO, 'N' GUBUN UNION ALL

SELECT '2015-01-03' DT, 1 SEQ, 'AAA' PARTNO, 'N' GUBUN UNION ALL

SELECT '2015-01-03' DT, 1 SEQ, 'CCC' PARTNO, 'Y' GUBUN UNION ALL

SELECT '2015-01-03' DT, 2 SEQ, 'AAA' PARTNO, 'N' GUBUN

) ,

res as

(

    SELECT PARTNO, DT, SEQ ,GUBUN

        ,  PARTNO+GUBUN newgb

        ,  ROW_NUMBER() over ( order by PARTNO, DT, SEQ) newseq

      FROM TEST) ,

result as

(     

SELECT a.* 

, CASE WHEN a.newgb = b.newgb THEN 0 ELSE 1 END no

 FROM res a left join res b on a.newseq    = b.newseq  + 1

)

SELECT r.PARTNO     

     , r.GUBUN     

     , COUNT(*) [연속cnt]

 FROM (

        SELECT a.PARTNO    

             , a.GUBUN      

             , SUM(b.no)  seq

          FROM result a left join result b on a.newseq >  b.newseq - 1

         GROUP BY a.PARTNO  , a.GUBUN  , a.newseq ) r 

GROUP BY r.PARTNO  , r.GUBUN ,  seq

아...이렇게 풀어야 하는군요....

두 분 모두 정말 정말 감사 드립니다.

아직 갈길이 멀었다는걸 또 느끼네요.

찬찬히 뜯어보면서 연구하겠습니다.

좋은 하루 되십시요..