67 lines
1.8 KiB
SQL
67 lines
1.8 KiB
SQL
-- Audit Bank Account Balances
|
|
-- Run this BEFORE applying balance constraints
|
|
-- Fix any inconsistencies found before running 004_balance_constraints.sql
|
|
|
|
-- Check for negative available balances
|
|
SELECT
|
|
id,
|
|
account_number,
|
|
currency_code,
|
|
balance,
|
|
available_balance,
|
|
reserved_balance,
|
|
'negative_available' as issue_type
|
|
FROM bank_accounts
|
|
WHERE available_balance < 0
|
|
ORDER BY available_balance ASC;
|
|
|
|
-- Check for negative reserved balances
|
|
SELECT
|
|
id,
|
|
account_number,
|
|
currency_code,
|
|
balance,
|
|
available_balance,
|
|
reserved_balance,
|
|
'negative_reserved' as issue_type
|
|
FROM bank_accounts
|
|
WHERE reserved_balance < 0
|
|
ORDER BY reserved_balance ASC;
|
|
|
|
-- Check for available > balance
|
|
SELECT
|
|
id,
|
|
account_number,
|
|
currency_code,
|
|
balance,
|
|
available_balance,
|
|
reserved_balance,
|
|
available_balance - balance as excess,
|
|
'available_exceeds_balance' as issue_type
|
|
FROM bank_accounts
|
|
WHERE available_balance > balance
|
|
ORDER BY (available_balance - balance) DESC;
|
|
|
|
-- Check for (available + reserved) > balance
|
|
SELECT
|
|
id,
|
|
account_number,
|
|
currency_code,
|
|
balance,
|
|
available_balance,
|
|
reserved_balance,
|
|
(available_balance + reserved_balance) - balance as excess,
|
|
'total_exceeds_balance' as issue_type
|
|
FROM bank_accounts
|
|
WHERE (available_balance + reserved_balance) > balance
|
|
ORDER BY ((available_balance + reserved_balance) - balance) DESC;
|
|
|
|
-- Summary count
|
|
SELECT
|
|
COUNT(*) FILTER (WHERE available_balance < 0) as negative_available_count,
|
|
COUNT(*) FILTER (WHERE reserved_balance < 0) as negative_reserved_count,
|
|
COUNT(*) FILTER (WHERE available_balance > balance) as available_exceeds_balance_count,
|
|
COUNT(*) FILTER (WHERE (available_balance + reserved_balance) > balance) as total_exceeds_balance_count,
|
|
COUNT(*) as total_accounts
|
|
FROM bank_accounts;
|