请帮助UNION将第二组代码作为一个存储过程代码添加到第一组代码中 [英] Please help to UNION the second set of code to the first set of code as one stored procedure code

问题描述

我想得到这个试算平衡的借方和贷方的最终总和。当我在sql中执行此操作时，总计会显示在不同的网格中，但我希望它们作为一个存储过程。



提前感谢

I want to get the final sum totals of of debit and credit of this trialbalance. When I execute this in sql the totals always show in a different gird but i want them as one stored procedure.

Thanks in advance

CREATE PROCEDURE [dbo].[TrialBalance]

AS

SELECT COA.Account_Code AS [Account Code],COA.Account_Name 
AS [Account Name],CAST(SUM(debit-credit) AS varchar) 
AS [$ Debit],'' AS [$ Credit] FROM COA JOIN CashBook 
ON COA.Account_Name = CashBook.Account_Name 
WHERE COA.Account_Name_Cate IN ('Assets') 
GROUP BY COA.Account_Code, COA.Account_Name

UNION ALL

SELECT COA.Account_Code AS [Account Code],COA.Account_Name 
AS [Account Name],CAST(SUM(debit-credit) AS varchar) 
AS [$ Debit],'' AS [$ Credit] FROM COA JOIN AccountReceivable_LDG 
ON COA.Account_Name = AccountReceivable_LDG.Account_Name 
WHERE COA.Account_Name_Cate IN ('Assets') 
GROUP BY COA.Account_Code, COA.Account_Name

UNION ALL 

SELECT COA.Account_Code 
AS [Account Code],COA.Account_Name 
AS [Account Name],'',CAST(SUM(debit-credit) AS varchar)
FROM COA JOIN AccountPayable_LDG ON COA.Account_Name = AccountPayable_LDG.Account_Name 
WHERE COA.Account_Name_Cate IN ('Liabilities') 
GROUP BY COA.Account_Code, COA.Account_Name



SELECT 'Totals :' AS [-],':' AS [-],CAST(SUM(Debit-Credit) AS varchar) 
AS [-] FROM ( SELECT  Debit, Credit  
FROM   COA JOIN CashBook ON COA.Account_Name = CashBook.Account_Name 
WHERE   COA.Account_Name_Cate IN ('Assets') 

UNION ALL

SELECT  Debit, Credit  
FROM   COA JOIN AccountReceivable_LDG ON COA.Account_Name = AccountReceivable_LDG.Account_Name 
WHERE   COA.Account_Name_Cate IN ('Assets'))s


SELECT 'Totals :' AS [-],':' AS [-],CAST(SUM(Debit-Credit) AS varchar) AS [-] 
FROM ( SELECT  Debit, Credit FROM   COA JOIN AccountPayable_LDG 
ON COA.Account_Name = AccountPayable_LDG.Account_Name 
WHERE   COA.Account_Name_Cate IN ('Liabilities'))s

添加代码块[/ edit]

[edit]Code block added[/edit]

推荐答案

借方]，' ' AS

Credit] FROM COA JOIN CashBook
ON COA.Account_Name = CashBook.Account_Name
WHERE COA.Account_Name_Cate IN （' 资产'）
GROUP BY COA.Account_Code，COA.Account_Name

UNION ALL

SELECT COA.Account_Code AS [帐户代码]，COA.Account_Name
AS [帐户名称]，CAST（SUM（借方贷方） AS varchar ）
AS

Credit] FROM COA JOIN CashBook ON COA.Account_Name = CashBook.Account_Name WHERE COA.Account_Name_Cate IN ('Assets') GROUP BY COA.Account_Code, COA.Account_Name UNION ALL SELECT COA.Account_Code AS [Account Code],COA.Account_Name AS [Account Name],CAST(SUM(debit-credit) AS varchar) AS

借记卡] ，' ' AS

这篇关于请帮助UNION将第二组代码作为一个存储过程代码添加到第一组代码中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！