[MS-SQL][Query] 멀티 데이터베이스 일별 집계 테이블 채크 쿼리 (Daily Table Check Query)
2018. 6. 1. 16:41
본체 | DeskTop |
---|---|
O S | Windows7 Ultimate K (Service Pack 1) |
APP | Microsoft SQL Server Management Studio 2012 |
MS-SQL | Micorsoft SQL Server 2008 R2 (10.50.1765.0) |
Daily Table Check Query
Daily Data가 쌓이는 Table에 빠진 데이터가 있는지 확인해보는 쿼리를 만들어보자.
SM(System Management)업무를 진행할때면 매일 집계되야하는 데이터가 집계가 안될경우가 생길때가 있다.
특히 데이터 베이스가 여러개일때 각각 데이터베이스마다 접속해서 확인하기 너무 귀찮다.
그럴때 아래 쿼리를 이용하여 비어있는 부분을 찾아서 해당날짜의 데이터를 재집계 해주면된다.
혹은 Check 프로그램을 만들때도 유용하다.
(Distinct을 이용하는 쿼리이기 때문에 너무 많은 데이터를 검색하면 문제가 생길수도 있다.)
※ fnStringToTable() 이라는 함수를 이용한다. 해당 함수의 자세한 내용은 아래 관련글 링크를 찾아가 확인 가능하다.
-- 동적으로 최근 날짜별,시간별 데이터 채크 로직 -- ------------------------------------------ 설정값 DECLARE @ViewDay Integer = 14 -- 몇일전까지 보고 싶은지 DECLARE @ViewTable Varchar(max) = 'DB1.dbo.TABLE1, DB2.dbo.TABLE2, DB3.dbo.TABLE3' -- 보고싶은 테이블 리스트 DECLARE @DateColumn Varchar(max) = 'updateTime' -- 날짜,시간 채크 컬럼명 (DateTime 형이 아닐경우 아래 query문 수정필요) ----------------------------------------------------------- DECLARE @Cnt Integer DECLARE @PivString Varchar(max) = '' DECLARE @ColString Varchar(max) = '' DECLARE @StartDate Datetime = DATEADD(DD, DATEDIFF(DD, 0, GETDATE() - @ViewDay), 0) DECLARE @Sql Varchar(max) = '' DECLARE @Title Varchar(max) = '' DECLARE @TableList Table ( Title Varchar(max) ,Idx Int IDENTITY(1,1) ) -------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------- 금일까지 Column String 구하기 (Pivot String 포함) -------------------------------------------------------------------------------------------------------------------- WHILE @StartDate < DATEADD(MS, -3, DATEADD(DD, DATEDIFF(DD, 0, GETDATE()) + 1, 0)) BEGIN SET @PivString = @PivString + '[' + Convert(CHAR(8), @StartDate, 112) + '],' SET @ColString = @ColString + '[' + Convert(CHAR(8), @StartDate, 112) + '] [' + Right(Convert(CHAR(8), @StartDate, 112), 2) + '],' SET @StartDate = DATEADD(DAY, 1 , @StartDate) END -- 마지막 ',' 빼주기 --- SET @PivString = Left(@PivString, LEN(@PivString) - 1 ) SET @ColString = Left(@ColString, LEN(@ColString) - 1 ) -------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------ String to Table Function 이용하여 Table List Table 가져오기 -------------------------------------------------------------------------------------------------------------------- INSERT INTO @TableList SELECT col AS Title FROM dbo.fnStringToTable(@ViewTable, ',') -------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- Query 생성 -------------------------------------------------------------------------------------------------------------------- SET @cnt = 1 WHILE @Cnt <= (SELECT Count(*) FROM @TableList) BEGIN SET @Title = (SELECT Title FROM @TableList WHERE Idx = @Cnt) SET @Sql = @Sql + N' SELECT Title ,' + @ColString + ' FROM ( SELECT Convert(CHAR(8), ' + @DateColumn + ', 112) Date ,''o'' Value ,''' + @Title + ''' Title FROM ( SELECT DISTINCT ' + @DateColumn + ' FROM ' + @Title + ' WHERE ' + @DateColumn + ' >= ' + Convert(CHAR(10), @StartDate, 25) + ' ) a ) a PIVOT ( Max(Value) FOR Date in ( ' + @PivString + ' ) ) PV ' SET @Cnt = @Cnt + 1 IF @Cnt <= (SELECT Count(*) FROM @TableList) SET @Sql = @Sql + N' UNION ALL ' END -------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------- Query 실행 -------------------------------------------------------------------------------------------------------------------- PRINT @Sql EXEC (@Sql)
결과는 아래와 같이 표시 된다. (데이터가 없는 부분은 null로 표시된다.)
※오늘날짜는 null이여야 정상이다. (오늘 저녁 12시가 지나야 오늘 데이터를 집계할테니..)
Title | 05 | 06 | 07 | 08 | 09 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | 17 | 18 | 19 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
DB1.dbo.TABLE1 | o | o | o | o | o | o | o | o | o | o | o | o | o | o | null |
DB2.dbo.TABLE2 | o | o | o | o | o | o | o | o | o | o | o | o | o | o | null |
DB3.dbo.TABLE3 | o | o | o | null | o | o | o | o | o | o | o | o | o | o | null |
보고 있는 글과 같이 보면 좋은 글들..
'MS-SQL > Sample' 카테고리의 다른 글
[MS-SQL] 루프문(WHILE)로 일일 집계 쿼리 일년치 한번에 돌리기 (0) | 2019.05.02 |
---|---|
[MS-SQL][Query] 멀티 데이터베이스 시간별 집계 테이블 채크 쿼리 (Hourly Table Check Query) (0) | 2018.06.04 |
[MS-SQL][FUNCTION] String to Table 변환 함수 (Split) (0) | 2018.06.01 |