请帮我解决这个问题 [英] Please help me to solve this once and foe all

问题描述

ID日期参考账户_代码账户_名称详情借方贷方余额

1 2013-04-19 NULL 1003应收账款ffrew 2000 0 2000

2 2013-04-19 NULL 1003应收帐款wwrt 0 1000 1000



这是我上面显示的表格样本，我想要总共3个表格作为总资产

这是我的代码如下：

ID Date Ref Account_Code Account_Name Details Debit Credit Balance
1 2013-04-19 NULL 1003 Account Receivable ffrew 2000 0 2000
2 2013-04-19 NULL 1003 Account Receivable wwrt 0 1000 1000

This is the sample of my tables shown above and i want the total of the 3 tables as total assets
and this is my code below:

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')

推荐答案

UNION ALL

SELECT COA.Account_Name AS ' ASSET'，CAST（SUM（借方 - 贷方） AS varchar ） AS

UNION ALL SELECT COA.Account_Name AS 'ASSET',CAST(SUM(Debit-Credit)AS varchar)AS

FROM COA JOIN CashBook < span class =code-keyword> ON COA.Account_Name = CashBook.Account_Name
WHERE COA.Account_Name IN （' Cash-in-Hand'）
< span class =code-keyword> GROUP BY COA.Account_Name

UNION ALL

SELECT COA.Account_Name AS ' ASSET'，CAST（SUM（借方 - 贷方） AS varchar ）< span class =code-keyword> 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 （' 应收帐款'）
GROUP BY COA.Account_Name

UNION ALL

SELECT COA。 Account_Name AS ' ASSET'，CAST（ SUM（借方贷方） 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

这篇关于请帮我解决这个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！