안녕하세요. 아직 SQL 입문도 못한 직딩입니다.
다름이 아니라
특정 기간을 파라미터로 받아서
해당 기간에 해당하는 날짜의 MH를 구하려고 하는데
기존에 있는 쿼리는 너무 하드쿼리로 되어있어서
동적쿼리로 변경하려고 하고 있습니다.
기존에 있는 쿼리는
SELECT B.Prono, B.Proname,
SUM(CASE WHEN A.ProDt = '" & Date4 & "' THEN MD ELSE 0 END) AS A,
SUM(CASE WHEN A.ProDt = '" & Date4+1 & "' THEN MD ELSE 0 END) AS B,
SUM(CASE WHEN A.ProDt = '" & Date4+2 & "' THEN MD ELSE 0 END) AS C,
SUM(CASE WHEN A.ProDt = '" & Date4+3 & "' THEN MD ELSE 0 END) AS D,
SUM(CASE WHEN A.ProDt = '" & Date4+4 & "' THEN MD ELSE 0 END) AS E,
SUM(CASE WHEN A.ProDt = '" & Date4+5 & "' THEN MD ELSE 0 END) AS F,
SUM(CASE WHEN A.ProDt = '" & Date4+6 & "' THEN MD ELSE 0 END) AS G,
SUM(CASE WHEN A.ProDt = '" & Date4+7 & "' THEN MD ELSE 0 END) AS H,
SUM(CASE WHEN A.ProDt = '" & Date4+8 & "' THEN MD ELSE 0 END) AS I,
SUM(CASE WHEN A.ProDt = '" & Date4+9 & "' THEN MD ELSE 0 END) AS J,
SUM(CASE WHEN A.ProDt = '" & Date4+10 & "' THEN MD ELSE 0 END) AS K,
SUM(CASE WHEN A.ProDt = '" & Date4+11 & "' THEN MD ELSE 0 END) AS L,
SUM(CASE WHEN A.ProDt = '" & Date4+12 & "' THEN MD ELSE 0 END) AS M,
SUM(CASE WHEN A.ProDt = '" & Date4+13 & "' THEN MD ELSE 0 END) AS N,
SUM(CASE WHEN A.ProDt = '" & Date4+14 & "' THEN MD ELSE 0 END) AS O,
SUM(CASE WHEN A.ProDt = '" & Date4+15 & "' THEN MD ELSE 0 END) AS P,
SUM(CASE WHEN A.ProDt = '" & Date4+16 & "' THEN MD ELSE 0 END) AS Q,
SUM(CASE WHEN A.ProDt = '" & Date4+17 & "' THEN MD ELSE 0 END) AS R,
SUM(CASE WHEN A.ProDt = '" & Date4+18 & "' THEN MD ELSE 0 END) AS S,
SUM(CASE WHEN A.ProDt = '" & Date4+19 & "' THEN MD ELSE 0 END) AS T,
SUM(CASE WHEN A.ProDt = '" & Date4+20 & "' THEN MD ELSE 0 END) AS U,
SUM(CASE WHEN A.ProDt = '" & Date4+21 & "' THEN MD ELSE 0 END) AS V,
SUM(CASE WHEN A.ProDt = '" & Date4+22 & "' THEN MD ELSE 0 END) AS W,
SUM(CASE WHEN A.ProDt = '" & Date4+23 & "' THEN MD ELSE 0 END) AS X,
SUM(CASE WHEN A.ProDt = '" & Date4+24 & "' THEN MD ELSE 0 END) AS Y,
SUM(CASE WHEN A.ProDt = '" & Date4+25 & "' THEN MD ELSE 0 END) AS Z,
SUM(CASE WHEN A.ProDt = '" & Date4+26 & "' THEN MD ELSE 0 END) AS AA,
SUM(CASE WHEN A.ProDt = '" & Date4+27 & "' THEN MD ELSE 0 END) AS AB,
SUM(CASE WHEN A.ProDt = '" & Date4+28 & "' THEN MD ELSE 0 END) AS AC,
SUM(CASE WHEN A.ProDt = '" & Date4+29 & "' THEN MD ELSE 0 END) AS AD,
SUM(CASE WHEN A.ProDt = '" & Date4+30 & "' THEN MD ELSE 0 END) AS AE,
SUM(CASE WHEN A.prodt between '" & Date4 & "' and '" & Date3 & "' then MD ELSE 0 END) AS TA
FROM dbo.Biz_Project AS B LEFT OUTER JOIN dbo.Biz_Project_Detail AS A ON A.ProNo = B.Prono INNER JOIN dbo.Biz_User AS C ON A.UserName = C.Name
WHERE (1 = 1) AND (A.NewGB = '0') AND C.Name = '이름'
GROUP BY B.Proname, B.Prono
위 쿼리와 같이 되어있고요.
결과는
---------------------------------------------------------------
ProNo Proname 2017-01-01 2017-01-02 ..... TA(SUM)
---------------------------------------------------------------
위와 같이 결과를 내고 싶은데 잘 안되네요;;
고수님들 배움의 기회를 주셨으면 좋겠습니다.
-- 대충 참고하세요
DECLARE @CNT INT
,@SQL NVARCHAR(MAX)
,@DATE3 DATETIME
,@DATE4 DATETIME
SET @SQL = N''
SET @CNT = 0
WHILE(@CNT <= 30) BEGIN
SET @SQL = @SQL + N'
,(CASE WHEN A.ProDt = DATEADD(DAY, @DATE4, ' + CAST(@CNT AS VARCHAR) + ') THEN MD ELSE 0 END) AS '
+ CASE WHEN @CNT >= 26 THEN CHAR(@CNT / 26 - 1 + 65) ELSE '' END + CHAR((@CNT % 26) + 65)
SET @CNT = @CNT + 1
END
SET @SQL = N'SELECT B.Prono, B.Proname'
+ @SQL + N'
SUM(CASE WHEN A.prodt between @DATE4 and @DATE3 then MD ELSE 0 END) AS TA
FROM dbo.Biz_Project AS B LEFT OUTER JOIN dbo.Biz_Project_Detail AS A ON A.ProNo = B.Prono INNER JOIN dbo.Biz_User AS C ON A.UserName = C.Name
WHERE (1 = 1) AND (A.NewGB = ''0'') AND C.Name = ''이름''
GROUP BY B.Proname, B.Prono
'
EXEC SP_ExecuteSql @SQL
,N'@DATE3 DATETIME, @DATE4 DATETIME'
,@DATE3, @DATE4