View: dbo.Bank_Transaction_Firm_Client_Uncleared_General_View
View definition
--Filtered indexed view that will allow the optimizer to have some idea of how many live bank
--transactions there are for the firm client that match up with at least one uncleared
--Bank_Transaction_Reconciliation_Detail row. The firm client checkbooks have a lot
--more transactions than a general checkbook so this view allows for more accurate statistics.
CREATE VIEW dbo.Bank_Transaction_Firm_Client_Uncleared_General_View
WITH SCHEMABINDING
AS
SELECT
bt.bank_transaction_KEY,
bt.transaction_date,
bt.bank_transaction_type_KEY,
bt.checkbook_KEY,
bt.transaction_status_KEY,
bt.amount,
bt.data_origin_KEY,
bt.reference_number,
bt.description,
bt.auto_clear_in_bank_rec,
c.client_KEY
FROM dbo.Bank_Transaction bt
INNER JOIN dbo.Checkbook cb
ON cb.checkbook_KEY = bt.checkbook_KEY
INNER JOIN dbo.Client c
ON c.client_KEY = cb.client_KEY
WHERE c.client_type_KEY = 2 -- only for firm client
AND bt.bank_reconciliation_clear_status_KEY <> 2; --We only care about bank transactions that are not completely cleared.