/****** Script for finding old data which the data_engine failed to delete ******/
use NimsoftSLM

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, @rows_out nvarchar(128);

declare @params nvarchar(256) = N'@rowcountOUT int, @count_out int OUTPUT'

set @raw_days = 150 -- Days where the data_engine should delete raw data
set @hist_days = 720 -- Days where the data_engine should delete historic data

-- Increment the static variables by '1' to compensate for a daily maintenance job that may not have started yet
set @raw_days = @raw_days + 1
set @hist_days = @hist_days + 1

-- 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)
         print 'RN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@raw_days AS varchar(4)) + ' days old.'
      set @index = @index + 1
   end

-- Reset our index variable to traverse HN tables

set @index = 1

-- 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 [NimsoftSLM].[dbo].[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)
         print 'HN_QOS_DATA_' + @tbl_char + ' contains ' + @rows_out + ' samples which are older than ' + CAST(@hist_days AS varchar(4)) + ' days old.'
      set @index = @index + 1
   end