[MS-SQL][Query] 멀티 데이터베이스 시간별 집계 테이블 채크 쿼리 (Hourly Table Check Query)

Mir's 운영환경

본체	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)

Hourly Table Check Query

---

Hourly Data가 쌓이는 Table에 빠진 데이터가 있는지 확인해보는 쿼리를 만들어보자.

SM(System Management)업무를 진행할때면 매일 집계되야하는 데이터가 집계가 안될경우가 생길때가 있다.

특히 데이터 베이스가 여러개일때 각각 데이터베이스마다 접속해서 확인하기 너무 귀찮다.

그럴때 아래 쿼리를 이용하여 비어있는 부분을 찾아서 해당날짜,시간의 데이터를 재집계 해주면된다.

혹은 Check 프로그램을 만들때도 유용하다.

(Distinct을 이용하는 쿼리이기 때문에 너무 많은 데이터를 검색하면 문제가 생길수도 있다.)

※ fnStringToTable() 이라는 함수를 이용한다. 해당 함수의 자세한 내용은 아래 관련글 링크를 찾아가 확인 가능하다.

    -- 동적으로 최근 날짜별,시간별 데이터 채크 로직 --
    ------------------------------------------ 설정값
    DECLARE @ViewDay Integer = 1 -- 몇일전까지 보고 싶은지
    DECLARE @ViewTable Varchar(max) = 'DB1.dbo.TABLE1, DB2.dbo.TABLE2'  -- 보고싶은 테이블 리스트
    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) )

    --------------------------------------------------------------------------------------------------------------------
    ----------------------------------------------------------------- 00시부터 24시까지 Column String 구하기 (Pivot String 포함)
    --------------------------------------------------------------------------------------------------------------------
    SET @Cnt = 0
    WHILE @Cnt < 24
    BEGIN
    SET @PivString = @PivString + '[' + Right('00' + Convert(Varchar, @Cnt), 2) + '],'
    SET @ColString = @ColString + '[' + Right('00' + Convert(Varchar, @Cnt), 2) + '] [' + Right('00' + Convert(Varchar, @Cnt), 2) + '],'
    SET @Cnt = @Cnt + 1
    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
        ,Date
        ,' + @ColString + '
    FROM (
        SELECT Right(Convert(CHAR(13), ' + @DateColumn + ', 20),2) Hour
                ,''o'' Value
                ,''' + @Title + ''' Title
                ,Convert(CHAR(8),' + @DateColumn + ', 112) Date
            FROM (
            SELECT DISTINCT ' + @DateColumn + '
                    FROM ' + @Title + '
                    WHERE ' + @DateColumn + ' >= ''' + Convert(Varchar(10), @StartDate , 121) + '''
                ) a
        ) a
    PIVOT ( Max(Value) FOR Hour 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이여야 정상이다.

Title	Date	00	01	02	03	04	05	~	18	19	20	21	22	23
DB1.dbo.TABLE1	20180611	o	o	o	o	o	o	~	o	o	o	o	o	o
DB1.dbo.TABLE1	20180612	o	o	o	o	o	o	~	null	null	null	null	null	null
DB2.dbo.TABLE2	20180611	o	o	o	null	null	o	~	o	o	o	o	o	o
DB2.dbo.TABLE2	20180612	o	o	o	o	o	o	~	null	null	null	null	null	null

보고 있는 글과 같이 보면 좋은 글들..

• [MS-SQL]멀티 데이터베이스 일별 집계 테이블 채크 쿼리 (Hourly Table Chack Query)

• [MS-SQL][Function] String to Table 변환 함수 (Split)

• [MS-SQL] 날짜 컨트롤 (Convert Datetime To String)

• [MS-SQL] 특정 일자 요일 구하기

• [MS-SQL] 특정 기간 범위 구하기

• [MS-SQL] GETDATE() 간단히 날짜 더하기 빼기