안녕하세요


SP를 곧 당장 작성해야하는데  너무 머리가 아프군요 ㅠㅠ


초보자 도움 부탁드려요




테이블에는 DATE, ID, TYPE , COUNT값이 있습니다.


이중 저는


어떤 순간에는


SELECT  DATE, ID   FROM TABLE

WHERE DATE = 20190201   AND TYPE =1 AND COUNT <100

GROUP BY DATE, ID 



즉 특정 TYPE 의 카운트 조건인 ID들이 필요하고


어떤 순간에는


DATE = 20190201

TYPE=1 AND COUNT <40 이면서    

TYPE=5 AND COUNT > 5    이런식의 조건을 가진 모든 ID  



또 어떤 순간에는

DATE = 20190201

TYPE=1 AND COUNT <40 이면서  

TYPE=5 AND COUNT > 5    이면서

TYPE=10 AND COUNT  <= 6  인 조건을 가진 모든 ID 


가 필요한 SP를 만들려고 합니다.



어떤 방향으로 해야할지 좀 감이안와서 ㅠㅠ 


조언을 주실 수 있을까요?





------------------------------------------------------------------------------------------------

제가 짠 sp 는 다음과 같은데용

좀 튜닝이 가능할지도요





  DEClARE @DATE INT
  DECLARE @nTYPE1 INT
  DEClARE @nCount1 INT
  DECLARE @nTYPE2 INT
  DEClARE @nCount2 INT
  DECLARE @nTYPE3 INT
  DEClARE @nCount3 INT
  DECLARE @nTYPE4 INT
  DEClARE @nCount4 INT
  DECLARE @nTYPE5 INT
  DEClARE @nCount5 INT

  SET @DATE = 20200222
  SET @nTYPE5 = ''
  SET @nCount5 = ''
  SET @nTYPE4 = ''
  SET @nCount4 = ''
  SET @nTYPE3 = 15
  SET @nCount3 = 1
  SET @nTYPE2 = 391
  SET @nCount2 = 1
  SET @nTYPE1 = 561
  SET @nCount1 = 3



  IF @nTYPE5 <> ''
  BEGIN
  SELECT  DATE, ID
  FROM [dbo].[table] WITH (NOLOCK)
  WHERE DATE=@DATE  AND TYPE=@nTYPE5 AND Count >= @nCount5 AND ID IN (SELECT ID FROM  [dbo].[table]
   WHERE DATE=@DATE  AND TYPE=@nTYPE4 AND Count >= @nCount4 AND ID IN (SELECT ID FROM  [dbo].[table]
    WHERE DATE=@DATE  AND TYPE=@nTYPE3 AND Count >= @nCount3 AND ID IN (SELECT ID FROM  [dbo].[table]
  WHERE DATE=@DATE  AND TYPE=@nTYPE2 AND Count >= @nCount2 AND ID IN (SELECT ID FROM  [dbo].[table]
   WHERE DATE=@DATE  AND TYPE=@nTYPE1 AND Count >= @nCount1 ))))
   GROUP BY DATE,ID
   END

   ELSE IF @nTYPE4 <> ''
     BEGIN
  SELECT  DATE, ID
  FROM [dbo].[table] WITH (NOLOCK)
  WHERE DATE=@DATE  AND TYPE=@nTYPE4 AND Count >= @nCount4 AND ID IN (SELECT ID FROM  [dbo].[table]
    WHERE DATE=@DATE  AND TYPE=@nTYPE3 AND Count >= @nCount3 AND ID IN (SELECT ID FROM  [dbo].[table]
  WHERE DATE=@DATE  AND TYPE=@nTYPE2 AND Count >= @nCount2 AND ID IN (SELECT ID FROM  [dbo].[table]
   WHERE DATE=@DATE  AND TYPE=@nTYPE1 AND Count >= @nCount1 )))
   GROUP BY DATE,ID
   END


   ELSE IF @nTYPE3 <> ''
     BEGIN
  SELECT  DATE, ID
  FROM [dbo].[table] WITH (NOLOCK)
    WHERE DATE=@DATE  AND TYPE=@nTYPE3 AND Count >= @nCount3 AND ID IN (SELECT ID FROM  [dbo].[table]
  WHERE DATE=@DATE  AND TYPE=@nTYPE2 AND Count >= @nCount2 AND ID IN (SELECT ID FROM  [dbo].[table]
   WHERE DATE=@DATE  AND TYPE=@nTYPE1 AND Count >= @nCount1 ))
   GROUP BY DATE,ID
   END

      ELSE IF @nTYPE2 <> ''
     BEGIN
  SELECT  DATE, ID
  FROM [dbo].[table] WITH (NOLOCK)
     WHERE DATE=@DATE  AND TYPE=@nTYPE2 AND Count >= @nCount2 AND ID IN (SELECT ID FROM  [dbo].[table]
   WHERE DATE=@DATE  AND TYPE=@nTYPE1 AND Count >= @nCount1 )
   GROUP BY DATE,ID
   END

ELSE 
     BEGIN
  SELECT  DATE, ID
  FROM [dbo].[table]WITH (NOLOCK)
       WHERE DATE=@DATE  AND TYPE=@nTYPE1 AND Count >= @nCount1
    GROUP BY DATE,ID
   END

   GO