Click here to Skip to main content
12,078,795 members (52,653 online)
Rate this:
 
Please Sign up or sign in to vote.
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(Debit-Credit) AS varchar) AS [$]
FROM COA JOIN CashBook JOIN AccountReceivable JOIN Allowance
ON COA.Account_Name = CashBook.Account_Name JOIN AccountReceivable.Account_Name JOIN Allowance.Account_Name
WHERE COA.Account_Name IN ('Assets')
Posted 22-Apr-13 10:04am
Comments
joshrduncan2012 22-Apr-13 15:12pm
   
What errors are you getting? What output (if any) are you getting?
ryanb31 22-Apr-13 15:18pm
   
If I understand you right, just wrap all of your sql into an outer SELECT statement and sum it all up.

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

The problem you are probably having is that when you +/- your credit or debit, one of those values could be null. If it is then the result will be null.

try in your queries:

SUM(ISNULL(Debit, 0) - ISNULL(Credit, 0))





p.s. Try not to double post. This I answered in the newer post.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web03 | 2.8.160212.1 | Last Updated 22 Apr 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100