--Check this table to see how the data_engine is doing on the return calls.
--SELECT * FROM TBNLOGGING order by RC 
--TRUNCATE TABLE TBNLOGGING
--388 rows returned

/****** Script for finding old data which the data_engine failed to summarize ******/
DECLARE @sql NVARCHAR(256),@tbl_char VARCHAR(4),@count INT,@count_out INT,@index INT = 1,@tbl_num INT,@raw_days INT,@hist_days INT,@daily_days INT,@rows_found TINYINT = 0,@rows_out NVARCHAR(128),@params NVARCHAR(256) = N'@rowcountOUT INT, @count_out INT OUTPUT';
 
SET @raw_days = (SELECT RawAge FROM tbn_de_DataMaintConfig) + 1; -- Days where the data_engine should delete raw data
SET @hist_days = (SELECT HistoryAge FROM tbn_de_DataMaintConfig) + 1; -- Days where the data_engine should delete hourly data
SET @daily_days = (SELECT DailyAvgAge FROM tbn_de_DataMaintConfig) + 1; -- Days where the data_engine should delete daily data
 
-- Set @tbl_num to be the number of raw tables (a count of the S_QOS_DEFINTION table)
SET @sql = N'SELECT @count_out = COUNT(*) FROM S_QOS_DEFINITION';
EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;
SET @tbl_num = CAST(@rows_out AS INT);
 
-- Search the RN tables for data that should have been removed
WHILE (@index <= @tbl_num) BEGIN
SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
SET @sql = N'SELECT @count_out = COUNT(*) FROM RN_QOS_DATA_' + @tbl_char + ' WHERE sampletime < (GETDATE() - ' + CAST(@raw_days AS VARCHAR(5)) + ')';
 
-- Make sure the table exists before running SQL against it ...
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'RN_QOS_DATA_'+ @tbl_char and xtype ='U')
EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;
 
-- Print the output of old raw data 
IF (CAST(@rows_out AS INT) > 0) BEGIN
PRINT 'RN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@raw_days AS VARCHAR(4)) + ' days old.';
SET @rows_found = 1;
END
 
SET @index = @index + 1;
END
 
IF (@rows_found = 0)
PRINT 'No unmaintained data found in RN tables';
 
-- Reset our index variable to traverse HN tables
SET @index = 1;
SET @rows_found = 0;
 
-- Search the HN tables for data that should have been removed
WHILE (@index <= @tbl_num) BEGIN
SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
SET @sql = N'SELECT @count_out = COUNT(*) FROM [HN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@hist_days AS VARCHAR(5)) + ')';
 
-- Make sure the table exists before running SQL against it ...
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'HN_QOS_DATA_' + @tbl_char and xtype = 'U')
EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;
 
-- Print the output of old historic data
IF (CAST(@rows_out AS INT) > 0) BEGIN
PRINT 'HN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS VARCHAR(4)) + ' days old.';
SET @rows_found = 1;
END
 
SET @index = @index + 1;
END
 
IF (@rows_found = 0)
PRINT 'No unmaintained data found in HN tables';
 
-- Reset our index variable to traverse DN tables
SET @index = 1;
SET @rows_found = 0;
 
-- Search the DN tables for data that should have been removed
WHILE (@index <= @tbl_num) BEGIN
SET @tbl_char = RIGHT('0000' + CONVERT(VARCHAR(4), @index), 4);
SET @sql = N'SELECT @count_out = COUNT(*) FROM [DN_QOS_DATA_' + @tbl_char + '] WHERE sampletime < (GETDATE() - ' + CAST(@daily_days AS VARCHAR(5)) + ')';
 
-- Make sure the table exists before running SQL against it ...
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'DN_QOS_DATA_' + @tbl_char and xtype = 'U')
EXEC sys.sp_executesql @sql, @params, @rowcountOUT = @count, @count_out = @rows_out OUTPUT;
 
-- Print the output of old historic data
IF (CAST(@rows_out AS INT) > 0) BEGIN
PRINT 'DN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS VARCHAR(4)) + ' days old.';
SET @rows_found = 1;
END
 
SET @index = @index + 1;
END
 
IF (@rows_found = 0)
PRINT 'No unmaintained data found in DN tables';
/****** End SQL Script ******/