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

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

※ 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] [Function] String to Table 변환 함수 (Split)

[MS-SQL] 현재 날짜, 시간 구하기 (GETDATE())

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

[MS-SQL] [Datetime To Varchar] 날짜를 일정형식의 문자로 만들기

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

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

날다의 운영환경
본체	DeskTop
O S	Windows7 Ultimate K
Application	Microsoft SQL Server Management Studio 2012
MS-SQL	Micorsoft SQL Server 2008 R2 (10.50.1765.0)