SQL - Health Overview for Evo dbs

go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Asamco BV - Alex
-- Create date: 25-01-2025
-- Description:	Health check function on possible Evo data issues.
-- Note: HEAVY query! Careful in usage!
-- =============================================
CREATE or ALTER FUNCTION dbo._as_Health_Overview
(
	-- Add the parameters for the function here
	@checkDate date /*only for queries requiring a check date.*/
)
RETURNS 
@tblHealth TABLE 
(
	-- Add the column definitions for the TABLE variable here
	ErrorType nvarchar(150), /*Critical, High, Warning, Info*/
	Module nvarchar(20), /*DB, AR, AP, GL, Budget, etc*/
	HealthMessage nvarchar(max),
	PossibleImpact nvarchar(max),
	NoOfIssues int,
	DetailQuery nvarchar(max),
	DetailData nvarchar(max),
	IsInfoOnly bit default 0
)
AS
BEGIN
	/*Usage:
	select * from dbo._as_Health_Overview(null)
	*/
	-- Fill the table variable with the rows for your result set

	--DATABASE HEALTH CHECKS
	--D1. Missing indices, more than x impact
	--D2. Fragmented indices
	--D3. Free space, if Express edition.
	--D4. ...

	--EVOLUTION DATA HEALTH CHECKS
	--1. Duplicate budget rows.
	;with dbr as (select x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId, count(*) NoOfDuplicates 
			from dbo._etblBudgets x 
			group by x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId
			having count(*) > 1)
	INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,IsInfoOnly)
		SELECT CASE WHEN EXISTS(SELECT * FROM DBR) THEN 'ERROR' ELSE 'OK' END
			,'Budgets',
			CASE WHEN EXISTS(SELECT * FROM DBR) THEN CONCAT('Duplicate budget rows exist.',(SELECT COUNT(*) FROM DBR), ' cases of duplication')
					ELSE 'OK. No duplicate budget rows exist.' END
			,Impact = CASE WHEN EXISTS(SELECT * FROM DBR) THEN 'Impact: Incorrect budget values & integration issues.' else '' END
			,NoOfIssues = COALESCE((SELECT COUNT(*) FROM DBR),0)
			,DetailQuery = N'SELECT * FROM _etblBudgets b
			LEFT JOIN (select x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId, count(*) NoOfDuplicates 
						from dbo._etblBudgets x 
						group by x.iBudgetAccountID,x.iBudgetProjectID, x.iBudgetPeriodID,x.iBudgetTxBranchId
						having count(*) > 1
						) dupes on b.iBudgetAccountID = dupes.iBudgetAccountID and b.iBudgetProjectID = dupes.iBudgetProjectID
											and b.iBudgetPeriodID = dupes.iBudgetPeriodID and b.iBudgetTxBranchID = dupes.iBudgetTxBranchID
			WHERE dupes.iBudgetAccountID is not null'
			,InfoOnly = CASE WHEN EXISTS(SELECT * FROM DBR) THEN 0 ELSE 1 END

	--2. Duplicate GL Account codes
	;with dGLr as (select g.Master_Sub_Account, count(*) NoOfDuplicates 
		from dbo.Accounts g group by g.Master_Sub_Account having count(*) > 1)
	INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
		SELECT CASE WHEN EXISTS(SELECT * FROM dGLr) THEN 'ERROR' ELSE 'OK' END
			,'GL',
			CASE WHEN EXISTS(SELECT * FROM dGLr) THEN CONCAT('Duplicate GL accounts exist.',(SELECT COUNT(*) FROM dGLr), ' cases of duplication')
					ELSE 'OK. No duplicate GL accounts exist.' END
			,Impact = CASE WHEN EXISTS(SELECT * FROM dGLr) THEN 'Impact: Incorrect report, integration, user confusion' else '' END
			,NoOfIssues = COALESCE((SELECT COUNT(*) FROM dGLr),0)
			,DetailQuery = N'select a.AccountLink, a.Master_Sub_Account,a.Description FROM Accounts a
			where a.Master_Sub_Account IN (select g.Master_Sub_Account
		from dbo.Accounts g group by g.Master_Sub_Account having count(*) > 1)'
			,DetailData = (select STRING_AGG(a.Master_Sub_Account,',') FROM dGLr a)
			,InfoOnly = CASE WHEN EXISTS(SELECT * FROM dGLr) THEN 0 ELSE 1 END
	--3. Duplicate AR Account codes
	;with dARr as (select ar.Account, count(*) NoOfDuplicates 
		from dbo.Client ar group by ar.Account having count(*) > 1)
	INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
		SELECT CASE WHEN EXISTS(SELECT * FROM dARr) THEN 'ERROR' ELSE 'OK' END
			,'AR',
			CASE WHEN EXISTS(SELECT * FROM dARr) THEN CONCAT('Duplicate AR accounts exist.',(SELECT COUNT(*) FROM dARr), ' cases of duplication')
					ELSE 'OK. No duplicate AR accounts exist.' END
			,Impact = CASE WHEN EXISTS(SELECT * FROM dARr) THEN 'Impact: Incorrect report, integration, user confusion' else '' END
			,NoOfIssues = COALESCE((SELECT COUNT(*) FROM dARr),0)
			,DetailQuery = N'select c.DCLink,c.Account,C.Name
			FROM Client c
			where c.Account IN (select ar.Account from dbo.Client ar group by ar.Account having count(*) > 1)'
			,DetailData = (select STRING_AGG(a.Account,',') FROM dARr a)
			,InfoOnly = CASE WHEN EXISTS(SELECT * FROM dARr) THEN 0 ELSE 1 END
	--4. Duplicate AP Account codes
	;with dAPr as (select ar.Account, count(*) NoOfDuplicates 
		from dbo.Vendor ar group by ar.Account having count(*) > 1)
	INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
		SELECT CASE WHEN EXISTS(SELECT * FROM dAPr) THEN 'ERROR' ELSE 'OK' END
			,'AP',
			CASE WHEN EXISTS(SELECT * FROM dAPr) THEN CONCAT('Duplicate AP accounts exist.',(SELECT COUNT(*) FROM dAPr), ' cases of duplication')
					ELSE 'OK. No duplicate AP accounts exist.' END
			,Impact = CASE WHEN EXISTS(SELECT * FROM dAPr) THEN 'Impact: Incorrect report, integration, user confusion' else '' END
			,NoOfIssues = COALESCE((SELECT COUNT(*) FROM dAPr),0)
			,DetailQuery = N'select c.DCLink,c.Account,C.Name
			FROM Vendor c
			where c.Account IN (select ar.Account from dbo.Vendor ar group by ar.Account having count(*) > 1)'
			,DetailData = (select STRING_AGG(a.Account,',') FROM dAPr a)
			,InfoOnly = CASE WHEN EXISTS(SELECT * FROM dAPr) THEN 0 ELSE 1 END
	--5. Audit trail out of balance.
	;with OOB as (SELECT cAuditNumber FROM PostGL p WHERE p.Period > 0 GROUP BY cAuditNumber HAVING ABS(ROUND(SUM(Debit-Credit),2)) > 0.01)
	INSERT INTO @tblHealth (ErrorType,Module,HealthMessage,PossibleImpact,NoOfIssues,DetailQuery,DetailData,IsInfoOnly)
		SELECT CASE WHEN EXISTS(SELECT * FROM OOB) THEN 'ERROR' ELSE 'OK' END
			,'GL',
			CASE WHEN EXISTS(SELECT * FROM OOB) THEN CONCAT('Audit Nos out of balance exist.',(SELECT COUNT(*) FROM OOB), ' cases.')
					ELSE 'OK. No Audit numbers out of balance.' END
			,Impact = CASE WHEN EXISTS(SELECT * FROM OOB) THEN 'Impact: TB out of balance!' else '' END
			,NoOfIssues = COALESCE((SELECT COUNT(*) FROM OOB),0)
			,DetailQuery = N'SELECT cAuditNumber, * FROM PostGL
			WHERE cAuditNumber IN ( SELECT cAuditNumber FROM PostGL GROUP BY cAuditNumber HAVING ABS(ROUND(SUM(Debit-Credit),2)) > 0.01
    )
    AND Period > 0 --excludes CB unreconciled tx from prior years.
    ORDER BY PostGL.cAuditNumber'
			,DetailData = CASE WHEN COALESCE((SELECT COUNT(*) FROM OOB),0) > 10 THEN 'First 10:' + 
					(select STRING_AGG(cAuditNumber,',') FROM (SELECT DISTINCT TOP 10  cAuditNumber FROM OOB) x)
						ELSE (select STRING_AGG(cAuditNumber,',') FROM OOB)
						END
			,InfoOnly = CASE WHEN EXISTS(SELECT * FROM OOB) THEN 0 ELSE 1 END

	RETURN 
END
GO