/*  -----------------------------------------------------------------------------------------------------------------------------------------------

The following SQL script compares two database and reports three kinds of changes:
* Records that have been CHANGED in the delta
* Records that have been ADDED to the data
* Records that have been REMOVED from the delta.

The following columns are reported:
  Delta_Rows_Tweaks:            The number of Rows in the Delta that exist in the Alpha and have had Changes
  Delta_Rows_Tweaks_Rate:               Delta_Rows_Tweaks / Delta_Rows_In_Alpha * 100.00
  Delta_Rows_Growth:                    The number of Rows in the Delta that were not in the Alpha (adds)
  Delta_Rows_Growth_Rate            Growth Percentage Calculation: Delta_Rows_Growth / Delta_Rows_In_Alpha * 100.00
  Delta_Rows_Shrinkage          The number of rows that were in the Alpha but not in the Delta (deletes)
  Delta_Rows_Shrinkage_Rate        Shrinkage Percentage Calculation: Delta_Rows_Shrinkage / Delta_Rows_In_Alpha * 100.00
  Alpha_Rows_Total            Total Row Counts in Alpha
  Delta_Rows_Total            Total Row Counts in Delta
  ----------------------------------------------------------------------------------------------------------------------------------------------- */
DECLARE @PHASE1_DB NVARCHAR(MAX) = 'ZZZ - F9F5D86F - 2026-07-13 @ 19.48.36.3609 - Abacus - FIRM2'
DECLARE @PHASE2_DB NVARCHAR(MAX) = 'ZZZ - F9F5D86F - 2026-07-13 @ 19.48.36.3609 - Abacus - FIRM3'

DECLARE @SCRIPT_INNER NVARCHAR(MAX) = N'

;WITH P AS (
   SELECT
    ''?'' Table_Name,
    SUM(ar.AlphaRow) Alpha_Rows_Total,
    SUM(dr.DeltaRow) Delta_Rows_Total,
    SUM(dira.DeltaRowInAlpha) Delta_Rows_In_Alpha,
    SUM(dd.DeltaDeleted) Delta_Rows_Shrinkage,
    SUM(dg.DeltaGrowth) Delta_Rows_Growth,
    SUM(rt.RowTweaked) Delta_Rows_Tweaks
  FROM
    [PHASE1_DB].? t1 WITH(NOLOCK)
      FULL OUTER JOIN
    [PHASE2_DB].? t2 WITH(NOLOCK)
      ON t1.Id = t2.Id
    CROSS APPLY(SELECT IIF(t1.Id IS NOT NULL, 1, 0) AlphaRow) ar
    CROSS APPLY(SELECT IIF(t2.Id IS NOT NULL, 1, 0) DeltaRow) dr
    CROSS APPLY(SELECT IIF(t1.Id IS NOT NULL AND t2.Id IS NOT NULL, 1, 0) DeltaRowInAlpha) dira
    CROSS APPLY(SELECT IIF(t1.Id IS NOT NULL AND t2.Id IS NULL, 1, 0) DeltaDeleted) dd
    CROSS APPLY(SELECT IIF(t1.Id IS NULL AND t2.Id IS NOT NULL, 1, 0) DeltaGrowth) dg
    CROSS APPLY(SELECT IIF(t1.Id IS NOT NULL AND t2.Id IS NOT NULL AND t1.[Hash] != t2.[Hash], 1, 0) RowTweaked) rt
)
SELECT
  P.Table_Name,
  P.Delta_Rows_Tweaks,
  ROUND(TRY_CAST(P.Delta_Rows_Tweaks AS FLOAT) / NULLIF(P.Delta_Rows_In_Alpha, 0) * 100.00, 2),
  P.Delta_Rows_Growth,
  ROUND(TRY_CAST(P.Delta_Rows_Growth AS FLOAT) / NULLIF(P.Delta_Rows_In_Alpha, 0) * 100.00, 2),
  P.Delta_Rows_Shrinkage,
  ROUND(TRY_CAST(P.Delta_Rows_Shrinkage AS FLOAT) / NULLIF(P.Delta_Rows_In_Alpha, 0) * 100.00, 2),
  P.Alpha_Rows_Total,
  P.Delta_Rows_Total
FROM
  P;
'

SET @SCRIPT_INNER = REPLACE(@SCRIPT_INNER, '''', '''''') 


DECLARE @SCRIPT NVARCHAR(MAX) = '


USE [PHASE1_DB]

DROP TABLE IF EXISTS #Results
CREATE TABLE #Results (
  Table_Name          NVARCHAR(517),
  Delta_Rows_Tweaks          BIGINT,
  Delta_Rows_Tweaks_Rate     DECIMAL(38, 2),
  Delta_Rows_Growth          BIGINT,
  Delta_Rows_Growth_Rate   DECIMAL(38, 2),
  Delta_Rows_Shrinkage       BIGINT,
  Delta_Rows_Shrinkage_Rate  DECIMAL(38, 2),
  Alpha_Rows_Total           BIGINT,
  Delta_Rows_Total           BIGINT
);

DECLARE @SCRIPT NVARCHAR(MAX) = ''
[SCRIPT_INNER]
''

INSERT INTO #Results (
    Table_Name,
    Delta_Rows_Tweaks, 
    Delta_Rows_Tweaks_Rate, 
    Delta_Rows_Growth, 
    Delta_Rows_Growth_Rate,
    Delta_Rows_Shrinkage, 
    Delta_Rows_Shrinkage_Rate, 
    Alpha_Rows_Total, 
    Delta_Rows_Total
) EXEC sp_MSforeachtable @SCRIPT


SELECT 
  * 
FROM
    #Results 
WHERE 1=1
  AND COALESCE(Alpha_Rows_Total, Delta_Rows_Total)  IS NOT NULL
ORDER BY 
  Table_Name

'

SET @SCRIPT = REPLACE(@SCRIPT, '[SCRIPT_INNER]', CONCAT('', @SCRIPT_INNER, ''))
SET @SCRIPT = REPLACE(@SCRIPT, '[PHASE1_DB]', CONCAT('[', @PHASE1_DB, ']'))
SET @SCRIPT = REPLACE(@SCRIPT, '[PHASE2_DB]', CONCAT('[', @PHASE2_DB, ']'))


EXEC sp_executesql @SCRIPT