데이터베이스 질문과 답변

MSSQL 쿼리 질문드립니다. 7

by 긍률 [SQLServer] MSSQL [2017.01.03 13:48:23]

--일근태(EMP_WORKPLAN) 의 STORECD='001'  데이타를 사원별로 가로로 표시하라.(사번(EMPNO), 일별시간(WORKTIME))
--프로시져의 매개변수로는 시작일자, 종료일자를 매개변수로 조회되도록 프로시져로 작성하라.(일자 변경 가능)

라는 예제를 작성중인대

WHILE 문에서 계속 에러가 납니다 ....

메시지 241, 수준 16, 상태 1, 프로시저 PIVOT_PROC, 줄 19
문자열을 날짜 및/또는 시간으로 변환하지 못했습니다.

뭐가 문제일까요 .... 도움을 구합니다 ㅠㅠ...

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
 
 
ALTER PROCEDURE PIVOT_PROC
       @SQL_SR   VARCHAR(5000)
     , @WHILE_SR VARCHAR(5000)
     , @SNUM_SR  VARCHAR(8)
     , @ENUM_SR  VARCHAR(8)
 
    AS
 
 WHILE  CONVERT(DATE,@SNUM_SR) <= CONVERT(DATE,@ENUM_SR)
 BEGIN 
 
   SET @WHILE_SR =@WHILE_SR+'['+@SNUM_SR+'],'
   SET @SNUM_SR=DATEADD(DD,1,CONVERT(DATE,@SNUM_SR))
   END
 
              
   SET @SQL_SR = 'SELECT EMPNO                         
                       , '''+@WHILE_SR+''' 
                    FROM (SELECT EMPNO, (WORKTIME) WORKTIME, YMD 
    FROM HRM_DB..EMP_WORKPLAN A WITH(NOLOCK) 
       WHERE STORECD = ''001'' 
     AND YMD BETWEEN ''' + @SNUM_SR +'''AND''' +@ENUM_SR +'''
 )A
PIVOT(SUM(WORKTIME) FOR YMD IN ('''+@WHILE_SR+''')) A'
EXEC @SQL_SR

 

 cs 
 

원하는 결과

EXEC PIVOT_PROC '','','20120101','20120108'  했을경우

EMPNO 20120101 20120102 20120103 20120104 20120105 20120106 20120107 20120108
21003010 360 360 0 360 360 360 0 360
21005014 0 0 0 360 360 360 0 0
21005119 660 660 0 0 0 0 0 660
21005174 360 360 0 0 0 0 0 0
21006066 360 0 660 360 360 0 360 360
21009007 310 0 0 390 360 360 360 385
21010007 0 390 0 360 360 327 360 0
by 마농 [2017.01.03 14:29:13]

1. @SQL_SR 과 @WHILE_SR 은 입력변수로 받을 필요가 없어 보이구요.
   별도 선언
2. @SNUM_SR 을 8자리로 문자로 받는데
  해당 변수에다가 하루 더해서 엎어치는 형태인데
  하루 더한 값은 날짜형이네요.
  8자리로 명확하게 문자변환한게 아니라서 에러날 듯 하구요.
  날짜와 문자가 혼용되어 사용되는 경우, 명확한 형변환을 해주는것이 좋습니다.
3. 입력변수의 값을 바꾼 후에 조건절에서 그대로 사용하네요.
  입력변수는 그대로 둔 채 바뀌는 변수를 따로 가져가야 할 듯 하네요.
4. @WHILE_SR 에 날짜를 컴마로 연결해 붙이는데요.
  맨 끝에 컴마가 남아 버리네요.
  맨 끝에 컴마를 제거해야 할 듯.

by 긍률 [2017.01.03 15:06:48]

감사합니다. 오류 수정하고 다른 작업 조금하니 결과 잘나오네요 ㅎㅎ 머리속으로 다시한번 그려보고 쿼리 작성해야 할것같습니다. 이것저것에서 실수가 많네요 ... 정말감사합니다.

by 마농 [2017.01.03 15:31:07]

성공 결과 소스 공유 부탁드려요.

저도 MSSQL 프로시져 한번도 짜본적이 없어서. 좋은 참고자료가 될 것 같습니다.

by 긍률 [2017.01.03 15:57:18]

허접하지만 .. 사족을 달아봤습니다.  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 

 
ALTER PROCEDURE PIVOT_PROC  --프로시저  선언
       @SNUM_SR  VARCHAR(8) --시작날
     , @ENUM_SR  VARCHAR(8) --끝나는날
        
    AS
 
       DECLARE @SQL_SR   VARCHAR(5000)=''     --NULL 방지(초기값 주지않으면 오류)
       DECLARE @WHILE_SR VARCHAR(5000)='' --NULL 방지
       DECLARE @CNUM_SR  VARCHAR(8)          --시작날 받을 변수
       SET @CNUM_SR = @SNUM_SR                    -- 시작날 대입 
 
 WHILE CONVERT(DATE, @SNUM_SR) < CONVERT(DATE,@ENUM_SR) 
 BEGIN  --반복문시작
   SET @WHILE_SR = @WHILE_SR+'['+@SNUM_SR+'],'
   SET @SNUM_SR  = CONVERT(VARCHAR(8), DATEADD(DD,1,CONVERT(DATE,@SNUM_SR)),112)
 
   END --반복문 종료
 
   SET @SQL_SR = 'SELECT EMPNO                         
                       , '+@WHILE_SR+'['+@ENUM_SR+']  
                    FROM (SELECT EMPNO, (WORKTIME) WORKTIME, YMD 
    FROM HRM_DB..EMP_WORKPLAN A WITH(NOLOCK) 
       WHERE STORECD = ''001'' 
     AND YMD BETWEEN ' + @CNUM_SR +' AND ' +@ENUM_SR +'
 )A
PIVOT(SUM(WORKTIME) FOR YMD IN ('+@WHILE_SR+'['+@ENUM_SR+'])) A'  
 
EXEC (@SQL_SR) -- 호출 
 
 EXEC PIVOT_PROC '20120101','20120108' -- 프로시저 호출 (시작날, 끝날)

cs

 

 

 

 

EMPNO 20120101 20120102 20120103 20120104 20120105 20120106 20120107 20120108
21003010 360 360 0 360 360 360 0 360
21005014 0 0 0 360 360 360 0 0
21005174 360 360 0 0 0 0 0 0
21006066 360 0 660 360 360 0 360 360
by 마농 [2017.01.04 10:16:12]

답변 드린 내용 잘 이해하시고, 잘 적용하셨네요.
약간 아쉬운 부분, 개선이 필요하다고 생각한 부분, 또 다른 구현 방법 등을 정리해 볼께요.


1. Select 부분 간단하게 변경 가능합니다.
   - 변경전 : 'SELECT EMPNO, '+@WHILE_SR+'['+@ENUM_SR+']
   - 변경후 : 'SELECT *
2. WORKTIME 부분은 왜 이렇게 작성한건지?
   - 변경전 : (WORKTIME) WORKTIME
   - 변경후 : WORKTIME
3. @WHILE_SR+'['+@ENUM_SR+'] 부분은
  - SQL 에서 직접 더하는것 보다는
  - 그전에 @WHILE_SR 자체에서 더해주고 끝내는게 좋을 듯
  - SET @WHILE_SR = @WHILE_SR+'['+@ENUM_SR+']'
4. @SNUM_SR 을 가공하셨는데
   - 입력변수인 @SNUM_SR 은 그대로 두고 @CNUM_SR 을 가공하는것이 좋을 듯 합니다.
5. 또다른 방법으로는 @CNUM_SR 변수 없이 @SNUM_SR 만 사용하는 법
   - 루프 돌리기전(@SNUM_SR 이 바뀌기전) PIVOT 이 제외된 SQL문을 완성한 뒤
   - 루프 돌린 후 PIVOT 을 붙이는 방법

by 긍률 [2017.01.04 11:05:05]

말씀하신대로 굳이 사용하지 않아도 되는 부분이 많았네요 ㅎㅎ 감사합니다. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
ALTER PROCEDURE PIVOT_PROC 
       @SNUM_SR  VARCHAR(8)
     , @ENUM_SR  VARCHAR(8)
        
    AS 
       DECLARE @SQL_SR   VARCHAR(5000)=''
       DECLARE @WHILE_SR VARCHAR(5000)=''
           SET @SQL_SR = 'SELECT *
                            FROM (SELECT EMPNO
                                       , WORKTIME
                                       , YMD
                                       FROM HRM_DB..EMP_WORKPLAN A WITH(NOLOCK) 
                                      WHERE STORECD = ''001'' 
                                        AND YMD BETWEEN ' + @SNUM_SR +' AND ' +@ENUM_SR +'
                                     )A
                                     '
                                     
 WHILE CONVERT(DATE, @SNUM_SR) < CONVERT(DATE, @ENUM_SR)
 BEGIN 
       SET @WHILE_SR = @WHILE_SR+'['+@SNUM_SR+'],'
       SET @SNUM_SR  = CONVERT(VARCHAR(8), DATEADD(DD,1,CONVERT(DATE,@SNUM_SR)),112)
   END 
   
   SET @WHILE_SR = @WHILE_SR+'['+@ENUM_SR+']'
   SET @SQL_SR = @SQL_SR + 'PIVOT(SUM(WORKTIME) FOR YMD IN ('+@WHILE_SR+')) A'
               
  EXEC (@SQL_SR)
  
  EXEC PIVOT_PROC '20120101','20120108'
 cs

 

EMPNO 20120101 20120102 20120103 20120104 20120105 20120106 20120107 20120108
21003010 360 360 0 360 360 360 0 360
21005014 0 0 0 360 360 360 0 0
21005174 360 360 0 0 0 0 0 0

 

by 마농 [2017.01.04 16:39:34]

YMD 조건을 숫자로 주셨네요.
이 경우 문자인 YMD 컬럼이 숫자로 형변환되어 비효율입니다.
YMD 조건을 문자로 주세요.
   - 변경전 : AND YMD BETWEEN ' + @SNUM_SR +' AND ' +@ENUM_SR +'
   - 변경후 : AND YMD BETWEEN ''' + @SNUM_SR +''' AND ''' +@ENUM_SR +'''

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입