안녕하세요
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
