SELECT 'ASSETS :' AS [Account Name],'Totals:' AS [$] UNION ALL SELECT COA.Account_Name AS 'ASSET',CAST(SUM(Debit-Credit)AS varchar)AS [$] FROM COA JOIN CashBook ON COA.Account_Name = CashBook.Account_Name WHERE COA.Account_Name IN ('Cash-in- Hand') GROUP BY COA.Account_Name UNION ALL SELECT COA.Account_Name AS 'ASSET',CAST(SUM(Debit-Credit)AS varchar)AS [$] FROM COA JOIN AccountReceivable ON COA.Account_Name = AccountReceivable.Account_Name WHERE COA.Account_Name IN ('Account Receivable') GROUP BY COA.Account_Name UNION ALL SELECT COA.Account_Name AS 'ASSET',CAST(SUM(Debit-Credit)AS varchar)AS [$] FROM COA JOIN Allowance ON COA.Account_Name = Allowance.Account_Name WHERE COA.Account_Name IN ('Allowance for Doubtful Account') GROUP BY COA.Account_Name UNION ALL SELECT '','------------' UNION ALL SELECT 'Total Assets',CAST(SUM(CashBook.Debit- CashBook.Credit+ AccountReceivable. Debit- AccountReceivable.Credit+ Allowance.Debit-Allowance.Credit) AS varchar) AS [$] FROM COA JOIN CashBook ON COA.Account_Name = CashBook.Account_Name JOIN AccountReceivable ON COA.Account_Name = AccountReceivable.Account_Name JOIN Allowance ON COA.Account_Name = Allowance.Account_Name WHERE COA.Account_Name_Cate IN ('Assets')
SUM(ISNULL(Debit, 0) - ISNULL(Credit, 0))
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)