1 | 12,39,57,29,33,43,19,32,20,23,12,19,13,8,8,9,9,8,8,9,8,8,9,8,8,8,9,8,8,9,8,8,8,9,8,8,9,9,8,9,8,8,8,9,8,8,9,8,8,9,8,8,12,9,10,8,9,9,10,10,2, | 2014-07-01 |
2 | 1,5,4,10,12,4,23,9,1,0,0,11,7,4,7,0,1,2,3,4,0,3,1,5,25,38,43,13,6, | 2014-07-02 |
3 | 18,29,13,27,23,14,11,9,9,8,8,8,9,8,8,9,8,8,8,9,8,9,9,8,8,9,8,8,8,9,8,8,9,8,8,9,8,8,8,9,8, | 2014-07-03 |
4 | 0,1,2,1,0,0,2,1,2,2,0,0,14,9,12,12,4,13,5,5,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,2,0,0,0,0,0,0,0,0,0,0,0,0, | 2014-07-04 |
5 | 12,39,57,29,33,43,19,32,20,23,12,19,13,8,8,9,9,8,8,9,8,8,9,8,8,8,9,8,8,9,8,8,8,9,8,8,9,9,8,9,8,8,8,9,8,8,9,8,8,9,8,8,12,9,10,8,9,9,10,10,22,23,12, 18,9,16,17,20,8,9,10,9,10,10,8,8,8,9,8,8,9,8,12,28,13,14,17,35,33,30,35,39,26,33,25,27,49,17,16,30,30,10,17,40,36,45,37,36,29,16,20,23,18,15, 24,17,8,16,52,39,36,19,20,14,69,45,49,29,26,47,25,16,16,34,24,33,22,15,19,13,8,9,8,8,8,9,8,8,9,8,8,9,8,8,8,9,8,8,9,8,9,8,9,8,8,9,8,8,9,8,8,8, | 2014-07-05 |
5분마다 들어오는 숫자를 ,로 구분하여 varchar(max)형으로 넣은 칼럼이 있습니다. 끝에도 ,로 들어가고요..
이걸 한 칼럼..그러니까 하루에 기록된 숫자의 합산을 구하려고 해서 검색하다가 문자열에서 숫자 합계 구하기(http://www.sqler.com/140487)를 발견해서 석이님이 리플로 달아두신 방법으로 쓰면 되겠다싶어 썼더니 아래와 같은 오류가납니다.
Msg 537, Level 16, State 3, Line 20
Invalid length parameter passed to the LEFT or SUBSTRING function.
그래서 이것저것 만저보다 쿼리 중에 CROSS APPLY ( SELECT TOP (2047) NUMBER S <이부분에 TOP (2047)을 80까지 줄이니 오류없이 합산이 되는데..이게 하루에 5분마다 들어온 숫자를 기록해서 다음날 전일 들어온 숫자 전부를 합산하는거라 80으로 줄이면 끝에 부분이 잘리는 문제가 있습니다. 오류를 검색해보니 해당 null 칼럼의 길이 값이 null이라 그렇다고 하는데 null값으로 들어간 것도 없는데 뭐가 문제인지 모르겠네요 오류없이 해결할 수 있는 방법이 없을까요? 5번 칼럼 정도의 길이가 매일 칼럼에 기록됩니다.
아래는 제가 사용한 구문입니다.
SET @RecordDate = CONVERT(VARCHAR(10), GETDATE()-1, 111)--yyyy/mm/dd
SELECT @OnlineMax =SUM(CAST(SUBSTRING(A.Daliy_CCU,B.S,B.E-B.S) AS INT))
FROM LOG_CCU A
CROSS APPLY (
SELECT TOP (80) NUMBER S
, CHARINDEX(',',A.Daliy+',',NUMBER + 1) E
FROM MASTER.DBO.SPT_VALUES
WHERE NUMBER = CHARINDEX (',',','+A.Daliy,NUMBER)
AND TYPE ='P'
ORDER BY NUMBER) B
WHERE DATEDIFF(DAY,@RecordDate, RegTime) = 0
GROUP BY A.DBIDX
WITH ROLLUP
완전 꼼수로 한번 해봤습니다.
DECLARE @DATA TABLE (순서 INT, 데이터 VARCHAR(8000))
INSERT @DATA SELECT 1, '12,39,57,29,33,43,19,32,20,23,12,19,13,8,8,9,9,8,8,9,8,8,9,8,8,8,9,8,8,9,8,8,8,9,8,8,9,9,8,9,8,8,8,9,8,8,9,8,8,9,8,8,12,9,10,8,9,9,10,10,2,'
INSERT @DATA SELECT 2, '1,5,4,10,12,4,23,9,1,0,0,11,7,4,7,0,1,2,3,4,0,3,1,5,25,38,43,13,6,'
INSERT @DATA SELECT 3, '18,29,13,27,23,14,11,9,9,8,8,8,9,8,8,9,8,8,8,9,8,9,9,8,8,9,8,8,8,9,8,8,9,8,8,9,8,8,8,9,8,'
INSERT @DATA SELECT 4, '0,1,2,1,0,0,2,1,2,2,0,0,14,9,12,12,4,13,5,5,5,0,0,0,0,0,0,0,0,0,0,0,0,0,0,3,2,0,0,0,0,0,0,0,0,0,0,0,0,'
INSERT @DATA SELECT 5, '12,39,57,29,33,43,19,32,20,23,12,19,13,8,8,9,9,8,8,9,8,8,9,8,8,8,9,8,8,9,8,8,8,9,8,8,9,9,8,9,8,8,8,9,8,8,9,8,8,9,8,8,12,9,10,8,9,9,10,10,22,23,12,18,9,16,17,20,8,9,10,9,10,10,8,8,8,9,8,8,9,8,12,28,13,14,17,35,33,30,35,39,26,33,25,27,49,17,16,30,30,10,17,40,36,45,37,36,29,16,20,23,18,15,24,17,8,16,52,39,36,19,20,14,69,45,49,29,26,47,25,16,16,34,24,33,22,15,19,13,8,9,8,8,8,9,8,8,9,8,8,9,8,8,8,9,8,8,9,8,9,8,9,8,8,9,8,8,9,8,8,8,'
DECLARE @SCRIPT NVARCHAR(MAX) = ''
DECLARE @SUM INT
SELECT @SCRIPT = @SCRIPT + REPLACE(데이터, ',', '+') FROM @DATA
SET @SCRIPT = 'SET @SUM = ' + @SCRIPT + '0'
EXEC SP_EXECUTESQL @SCRIPT, N'@SUM INT OUTPUT', @SUM = @SUM OUTPUT
SELECT SUM = @SUM