WITH  T
AS (
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160104' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160104' AS MEDDATE,'2' AS C4,'HA444' AS C5,'일반전산화단층영상' AS C6,'B01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160107' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160107' AS MEDDATE,'2' AS C4,'HA444' AS C5,'일반전산화단층영상' AS C6,'B01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160111' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160111' AS MEDDATE,'2' AS C4,'CX747' AS C5,'핵의학적방법' AS C6,'C01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160111' AS MEDDATE,'3' AS C4,'HA475' AS C5,'일반전산화단층영상-복부' AS C6,'B03' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160114' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160114' AS MEDDATE,'2' AS C4,'HA444' AS C5,'일반전산화단층영상' AS C6,'B01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160118' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160118' AS MEDDATE,'2' AS C4,'HA444' AS C5,'일반전산화단층영상' AS C6,'B01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160121' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160121' AS MEDDATE,'2' AS C4,'CX747' AS C5,'핵의학적방법' AS C6,'C01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160125' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160125' AS MEDDATE,'2' AS C4,'CX747' AS C5,'핵의학적방법' AS C6,'C01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160128' AS MEDDATE,'1' AS C4,'C5590' AS C5,'호르몬수용체검사' AS C6,'A01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160128' AS MEDDATE,'2' AS C4,'HA444' AS C5,'일반전산화단층영상' AS C6,'B01' AS GB FROM DUAL UNION ALL
SELECT '000001' AS C0,'10' AS C1,'1' AS C2,'20160128' AS MEDDATE,'3' AS C4,'CX747' AS C5,'핵의학적방법' AS C6,'C01' AS GB FROM DUAL 
)                  
SELECT GB, YMON
  FROM (SELECT GB, SUBSTR(MEDDATE,1,6) YMON
             , TO_CHAR (TO_DATE (MEDDATE, 'yyyymmdd'), 'iw') WEEK
             , COUNT (1) CNT
          FROM T
         GROUP BY GB, SUBSTR(MEDDATE,1,6), TO_CHAR (TO_DATE (MEDDATE, 'yyyymmdd'), 'iw')
       )
 GROUP BY GB, YMON
 HAVING COUNT (WEEK) = COUNT (CASE WHEN CNT >= 2 THEN 1 ELSE NULL END)

새해 복 많이 받으세요~~

병원시스템 운영하시나요? 많이 보던 edi코드와 명칭들이..

저도 병원시스템 운영중이예요ㅋㅋ 연휴인데 처방 안들어간다고 전화받고

잠깐 회사 컴터 붙었네요ㅠㅠ

일단 주차가 어떤 기준인지부터 정하셔야할 것 같구요.

(일요일부터 시작인지, 월요일부터 시작인지,

단순이 1일부터 7일 단위로 끊을 것인지,

2월 29일 같은 날은 2월 마지막주로 잡을 것인지 3월 첫주에 포함할 것인지, 

월요일이 주의 시작으로 보고 2월 29일을 5주차로 잡으면 29일에 처방이 2개

존재하지 않으면 매주 2회라는 조건때문에 제외되겠죠..)

저는 iw 기준으로 생각하고 했어요. 원하시는 결과물의 형태도

적어주시면 더 좋을 것 같고..

적어주시면 다른 분이 더 멋지고 깔쌈한 쿼리로 답변드릴 겁니다ㅋㅋ

수고하세용^^

아 설지나고 이제야 확인하네요~jkson님 늦었지만 정말감사드립니다. 설 연휴는 잘 쉬셨는지요

저도 병원전산관련 업무 하고 있습니다^^ 쿼리의 길은 멀고도 끝이 없네요~ㅎ

가르쳐 주신데로 적용한번 해봐야할것 같네요~

말씀해주신데로 기준이 조금 애매한 부분이 있네요~저도 다시 한번 확인은 해봐야할것 같은데

원래 기준이 해당 검사를 주2회 이상 월8회 이상 했는지에 대한 조건이라 디테일하게

생각을 못했습니다.  좀더 확인을 해보고 정리해봐야할것 같네요^^

정말 감사드립니다~(__) 

또 궁금한게 있어서 문의드립니다

만약 해당 검사 일자가 서로 일치하는 경우에 그 해당 일자를 가지고 오고 싶은데요

예를 들면

2016년 01월에 A01의 처음 사용한 일자와 B01 처음 사용한 일자가 같으면 '20160104' 일자를 가지고

오는 거지요. 아래 쿼리는 같은날에 하면 결과값이 1이고 다른날이면 결과값이 2 미시행일경우 결과 같이 3이 나오도록 한 MSSQL 쿼리문인데요 만약 A01과 B01 검사가 같은날 동시에 이루어졌을때 일자를 가지고 오는 쿼리는 어떻게 해야할까요? 그리고 아래 쿼리를 좀더 간결하게도 가능할까요? 도움 부탁드립니다 (__)

SELECT
  (CASE WHEN(
  (
   SELECT
        진료일자
   FROM 
        (
         SELECT
            ROW_NUMBER() OVER(ORDER BY 진료일자,일련번호 ASC) AS RN, 진료일자
         FROM   검사테이블
         WHERE  기관구분 = '000001'
         AND    분류코드 = '10'
         AND    차트번호 = '00000001'
         AND    LEFT(진료일자,6) = '201601'
         AND    구분코드 = 'A01'
         ) A
   WHERE    RN = 1    
     ) = (
     SELECT
             진료일자
     FROM
             (
              SELECT
                 ROW_NUMBER() OVER(ORDER BY 진료일자,일련번호 ASC) AS RN, 진료일자
              FROM   검사테이블
              WHERE  기관구분 = '000001'
              AND    분류코드 = '10'
              AND    차트번호 = '00000001'
              AND    LEFT(진료일자,6) = '201601'
              AND    구분코드 = 'B01'
             ) A
     WHERE    RN = 1
     ) 
     ) THEN 1
     WHEN
    SELECT         
        진료일자 
     FROM         
              ( SELECT             
                  ROW_NUMBER() OVER(ORDER BY 진료일자,일련번호 ASC) AS RN, 진료일자          
               FROM     검사테이블          
               WHERE    기관구분 = '000001'          
               AND      분류코드 = '10'          
               AND      차트번호 = '00000001' 
               AND      LEFT(진료일자,6) = '201601'          
               AND      구분코드 = 'A01'
               ) A 
      WHERE RN = 1    
      ) <> (
     SELECT
             진료일자
     FROM
             (
              SELECT
                    ROW_NUMBER() OVER(ORDER BY 진료일자,일련번호 ASC) AS RN, 진료일자
              FROM   검사테이블
              WHERE  기관구분 = '000001'
              AND    분류코드 = '10'
              AND    차트번호 = '00000001'
              AND    LEFT(진료일자,6) = '201601'
              AND    구분코드 = 'B01'
             ) A
     WHERE    RN = 1
     ) 
     ) THEN 2
     ELSE 3 END) 결과값