21 CREATE PROC usp_DisplayTablesNotLoading

Description: This proc can be ran to display what tables are not loading. It can be used as part of troubleshooting.

Necessary Modification: There are some tables you do not want to monitor for various reasons. To remove these tables from the results set, get their table ID from vol.Tables and insert them into the UnwantedTables temporary table.

--Manually get rid of unwanted tables.
INSERT INTO #UnWantedTables(TableID)
SELECT 131

USE [ODS]
GO


DROP PROCEDURE IF EXISTS [dbo].[usp_DisplayTablesNotLoading]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_DisplayTablesNotLoading] AS
BEGIN

CREATE TABLE #UnWantedTables(TableID BIGINT)
CREATE CLUSTERED INDEX CIDX_UNWANTEDTABLES_TABLEID ON #UnWantedTables(TableID)

DECLARE @MostRecentDate DATETIME

SELECT @MostRecentDate =  MAX(DateOfCurrentObservation) FROM [vol].[LoadObservations]

INSERT INTO #UnWantedTables(TableID)
--Get rid of tables with no records
SELECT t.TableID
FROM [vol].[Tables] t
JOIN [vol].[LoadObservations] lo
ON t.TableID = lo.TableID
GROUP BY t.TableID
HAVING AVG(CAST(lo.[RowCount] AS BIGINT)) = 0
UNION
--Get rid of tables with no load variance
SELECT t.TableID
FROM [vol].[Tables] t
JOIN [vol].[LoadObservations] lo
ON t.TableID = lo.TableID
GROUP BY t.TableID
HAVING AVG(lo.CurrentThreeSDLevel) = 0

--Manually get rid of unwanted tables.
--INSERT INTO #UnWantedTables(TableID)
--SELECT 131



SELECT TableID, [TableName]
FROM  [vol].[Tables]
WHERE TableID IN (
SELECT t.TableID
FROM [vol].[Tables] t
JOIN [vol].[LoadObservations] lo
ON t.TableID = lo.TableID
WHERE t.TableID NOT IN (SELECT TableID FROM #UnWantedTables)
AND lo.DateOfCurrentObservation BETWEEN DATEADD(DD,-3,CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP
GROUP BY t.TableID
HAVING AVG(CAST(lo.ChangeFromLastObservation AS FLOAT)) = 0
)

DROP TABLE #UnWantedTables

END




GO