I am trying to generate a report that will get me the current balances for my checking accounts, term accounts, investment accounts.
I think the main issue on why this code is not giving me the proper current value of my checking accounts is due to how the database is handling the "Transfer " transaction.
If the transfer is going from account A -> account B, then the transfer should be considered a withdrawal in account A and a payment in account B. This is the main constraint but when you try to query the transactions in account B, this transfer is nowhere to be seen since, as it is only marked in account A, therefore when you want to make the sum of payments and withdrawals and transfers from account B, the value will be incorrect.
Also, if the transfer is from one account to an external part (not in the accounts I setup) it should be considered a payment.
Here is a snip of the query:
--
WITH InvestmentBalances AS (
SELECT
i.ACCOUNTID,
i.ACCOUNTNAME AS InvestmentAccountName,
SUM((s.NUMSHARES * s.CURRENTPRICE) - i.INITIALBAL) AS InvestmentBalance
FROM ACCOUNTLIST_V1 AS i
INNER JOIN STOCK_V1 AS s ON i.ACCOUNTID = s.HELDAT
WHERE i.ACCOUNTTYPE = 'Investment' AND i.STATUS = 'Open'
GROUP BY i.ACCOUNTID, i.ACCOUNTNAME
HAVING InvestmentBalance >= 0
),
CheckingBalances AS (
SELECT
a.ACCOUNTID,
a.ACCOUNTNAME,
SUM(CASE WHEN c.TRANSCODE IN ('Deposit', 'Transfer') THEN c.TRANSAMOUNT
WHEN c.TRANSCODE = 'Withdrawal' THEN -c.TRANSAMOUNT
ELSE 0 END) AS CheckingBalance
FROM ACCOUNTLIST_V1 AS a
LEFT JOIN CHECKINGACCOUNT_V1 AS c ON a.ACCOUNTID = c.ACCOUNTID
WHERE a.ACCOUNTTYPE = 'Checking' AND a.STATUS = 'Open'
GROUP BY a.ACCOUNTID, a.ACCOUNTNAME
HAVING CheckingBalance >= 0
)
SELECT
'Investment' AS AccountType,
ib.InvestmentAccountName AS AccountName,
ib.InvestmentBalance AS Balance
FROM InvestmentBalances ib
UNION ALL
SELECT
'Checking' AS AccountType,
cb.ACCOUNTNAME AS AccountName,
cb.CheckingBalance AS Balance
FROM CheckingBalances cb
--
Any help would be much appreciated
