Query that detects difference between accounts/loads from TODAY and YESTERDAY

GOAL: DETECT any difference between yesterday’s table loads and today’s loads. Each load loads values of data that are associated with bank accounts. So I need a query that returns each individual account that has a difference, with the value in the column name.

I need data from several columns that are located from two different tables. AEI_GFXAccounts and AEI_GFXAccountSTP. Each time the table is loaded, it has a “run_ID” that is incremented by one. So it needs to be compared to MAX(run_id) and MAX(run_id) -1.

I have tried the following queries. All this query does is return all the columns I need. I now need to implement logic that runs these queries WHERE runID = MAX(runID). Then run it again where run_ID = Max(runID) -1. Compare the two tables, show the differences that can be displayed under columns like SELECT AccountBranch WHERE MAX(Run_ID) -1 AS WAS. etc. and another custom named column as ‘IS NOW‘ etc for each column.

SELECT AEI_GFXAccounts.AccountNumber,
  AccountBranch,
  AccountName,
  AccountType,
  CostCenter,
  TransactionLimit,
  ClientName,
  DailyCumulativeLimit
FROM AEI_GFXAccounts
JOIN AEI_GFXAccountSTP
  ON (AEI_GFXAccounts.feed_id = AEI_GFXAccountSTP.feed_id
  and AEI_GFXAccounts.run_id = AEI_GFXAccountSTP.run_id)

Answer

I use something similar to this to detect changes for a logging system:

WITH data AS (

    SELECT
      a.run_id,
      a.AccountNumber,
      ?.AccountBranch,
      ?.AccountName,
      ?.AccountType,
      ?.CostCenter,
      ?.TransactionLimit,
      ?.ClientName,
      ?.DailyCumulativeLimit
    FROM 
      AEI_GFXAccounts a
      INNER JOIN AEI_GFXAccountSTP b
      ON 
        a.feed_id = b.feed_id and 
        a.run_id = b.run_id
), 
yest AS (
  SELECT * FROM data WHERE run_id = (SELECT MAX(run_id)-1 FROM AEI_GFXAccounts)
),
toda AS (
  SELECT * FROM data WHERE run_id = (SELECT MAX(run_id) FROM AEI_GFXAccounts)
)

SELECT
  CASE WHEN COALESCE(yest.AccountBranch, 'x') <> COALESCE(toda.AccountBranch, 'x') THEN yest.AccountBranch END as yest_AccountBranch,
  CASE WHEN COALESCE(yest.AccountBranch, 'x') <> COALESCE(toda.AccountBranch, 'x') THEN toda.AccountBranch END as toda_AccountBranch,
  CASE WHEN COALESCE(yest.AccountName, 'x')   <> COALESCE(toda.AccountName, 'x')   THEN yest.AccountName   END as yest_AccountName,
  CASE WHEN COALESCE(yest.AccountName, 'x')   <> COALESCE(toda.AccountName, 'x')   THEN toda.AccountName   END as toda_AccountName,

  ...

FROM
  toda INNER JOIN yest ON toda.accountNumber = yestaccountNumber

Notes:

• You didn’t say which table some of your columns are from. I’ve prefixed them with ?. – replace these with a. or as. respectively (always good practice to fully qualify all your column aliases)
• When you’re repeating out the pattern in the bottom select (above …) choose data for the COALESCE that will not appear in the column. I’m using COALESCE as a quick way to avoid having to write CASE WHEN a is null and b is not null or b is null and a is not null or a != b, but the comparison fails if accountname (for example) was 'x' yesterday and today it is null, because the null becomes ‘x’. If you pick data that will never appear in the column then the check will work out because nulls will be coalesced to something that can never appear in the real data, and hence the <> comparison will work out
• If you don’t care when a column goes to null today from a value yesterday, or was null yesterday but is a value today, you can ditch the coalesce and literally just do toda.X <> yest.X
• New accounts today won’t show up until tomorrow. If you want them to show up do toda LEFT JOIN yest .... Of course all their properties will show as new 😉
• This query returns all the accounts regardless of whether any changes have been made. If you only want a list of accounts with changes you’ll need a where clause that is similar to your case whens:

    WHERE
      COALESCE(toda.AccountBranch, 'x') <> COALESCE(yest.AccountBranch, 'x') OR      
      COALESCE(toda.AccountName,   'x') <> COALESCE(yest.AccountName,   'x') OR
      ...