Kinyanjui Kamau's questions

我有以下查询

SELECT 'Total_Invoices', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_DownPayment', SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
DateDiff(MM,T0.DocDate,'2017-02-02')=0
UNION ALL
SELECT 'Total_CreditNotes', (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND 
DateDiff(MM,T0.DocDate,'2017-02-02')=0

输出是：

我想创建一个额外的列，添加第四列，其中包含三行的行总计。我尝试了以下无济于事：

;WITH CTE AS 
(
SELECT * FROM
(
    SELECT  SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM OINV T0 WHERE T0.[Series] <>'80'  AND
    DateDiff(MM,T0.DocDate,'2017-02-02')=0 
)AS Qry1
    UNION ALL
    SELECT * FROM
(
    SELECT SUM(T0.[DocTotal]) - (SUM(T0.[VatSum])-SUM(T0.[WTSum])) as "Amnt(Exl)", SUM(T0.[VatSum]) as "VAT", SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ODPI T0 WHERE
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry2
    UNION ALL
    SELECT * FROM
(
    SELECT (SUM(T0.[VatSum])-SUM(T0.[WTSum]))-SUM(T0.[DocTotal]) as "Amnt(Exl)", -SUM(T0.[VatSum]) as "VAT", -SUM(T0.[DocTotal]) as "Amnt(Incl)" FROM ORIN T0 WHERE T0.[Series]='5' AND 
    DateDiff(MM,T0.DocDate,'2017-02-02')=0
)AS Qry3
)
SELECT *, Qry1 + Qry2 + Qry3 AS TotalSum
FROM CTE

我如何达到第 4 列总数？

我有 2 个要连接的 SQL 语句。第一个获取营业额帐户每月的实际数据。第二个查询获取相同营业额帐户的预算（如果有）。

查询 1 实际值

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
FROM OACT T0
LEFT JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account] 
LEFT JOIN OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] 
WHERE T0.GroupMask = 4 AND T0.Levels = 5
AND (T1.RefDate BETWEEN '20150101' AND '20151231') 
GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate) AS q
PIVOT
(
    SUM(Amount) 
    FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

样本数据

查询 2 预算

/* Turnover 1 */
SELECT * FROM
(
SELECT T0.AcctCode AS 'AcctCode', T3.AcctName AS 'Description', T3.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0) THEN T1.DebLTotal ELSE T1.CredLTotal END AS BudgetAmt, T1.Line_ID AS 'LineID'
FROM OBGT T0 INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
INNER JOIN OACT T3 ON T0.[AcctCode] = T3.[AcctCode] 
WHERE T3.GroupMask = 4) AS q
PIVOT
(
    SUM(BudgetAmt)
    FOR LineID IN ([0],[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11])
) AS P

样本数据

对于第二个查询，0 代表一月，1 代表二月……而 11 代表十二月。

如何组合这 2 个查询以便我可以获得如下数据？

不重复，因为它包含检索预算的查询。

我在下面有这两个查询，用于计算从 1 月到 12 月的净利润。

查询 1

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T0.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T0.[Credit] - T0.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities] 
FROM JDT1 T0 WHERE (T0.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231') 
AND T0.[ContraAct] <> '311201'
GROUP BY T0.RefDate) AS q
PIVOT
(
SUM(Amount) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

结果：

查询 2

/* Profit for the Period 13 */
SELECT * FROM
(
SELECT MONTH(T1.RefDate) AS Month, '311999' AS 'SAP Code', 'Period Profit' AS 'Description', SUM (T1.[Credit] - T1.[Debit]) AS 'Amount',
'311200' AS 'FatherNum', 12 AS 'Group', 'Liabilities' [Liabilities] 
FROM OACT T0 INNER JOIN JDT1 T1 ON T0.[AcctCode] = T1.[Account]
WHERE (T1.RefDate BETWEEN DATEADD(yy, DATEDIFF(yy,0,'20151231'), 0) AND '20151231') 
AND T0.GroupMask > 3 
GROUP BY T1.RefDate) AS q 
PIVOT
(
SUM(Amount) 
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

结果：

两者之间的差异出现在第 12 个月。查询 1 准确地获得了 Dec 利润，因为它是一个在 12 月关闭 2015 年的过程后存储数据的帐户。

如何将两者合并为一个查询，以便获得查询 2 的所有结果，第 12 个月除外，而不是 -4764758.550000 我得到 3280335.630000？

我在下面有这个查询，我试图在其中获取迄今为止的月份和年份（账户余额）的结果。

SELECT T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-09-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
UNION 
SELECT  T3.AcctCode, T3.AcctName, SUM(T1.Debit - T1.Credit)* -1 AS 'YTD Balance'
FROM OJDT T0
INNER JOIN JDT1 T1 ON T0.[TransId] = T1.[TransId]
INNER JOIN OACT T2 ON T1.[Account] = T2.[AcctCode]
INNER JOIN OACT T3 ON T3.[AcctCode] = T2.[FatherNum]
WHERE T0.TaxDate BETWEEN '2014-01-01' AND '2014-09-30'
AND T3.AcctCode LIKE '611%'
GROUP BY T3.AcctCode, T3.AcctName
ORDER BY T3.AcctCode

结果如下：

我遇到的问题是 Balance 和 Year to Date 余额列堆叠在另一个之上。如何修改此查询以具有 4 列，即：AcctCode、AcctName、Balance、YTD Balance？使用 SQL Server 2008

我有两个要比较的查询，并获取在第一个查询中找到的额外行，这些行不在第二个加 1 中。我需要 DocEntryCN 来执行一些工作。

例如查询 1

SELECT DocEntry AS "DocEntryCN" , Comments, JrnlMemo, DocTotal
FROM ORIN WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL 
ORDER BY DocTotal ASC, Comments

查询 2

SELECT DocEntry AS "DocEntryINV" , Comments, DocTotal
FROM OINV WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL 
ORDER BY DocTotal ASC, Comments

示例结果：查询 1

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
70            reverse     kaka-19       500
71            reverse     kaka-19       500
74            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
79            reverse     Rob-23        750
84            reverse     Paul-17       1250
86            reverse     Paul-17       1250
87            reverse     Paul-17       1250

示例结果：查询 2

DocEntryINV   Comments       DocTotal
1             kaka-19        500
3             kaka-19        500
5             kaka-19        500
6             kaka-19        500
7             Rob-23         750
9             Rob-23         750
10            Alex-09        1000
11            Olma-08        1150
12            Paul-17        1250 
13            Paul-17        1250
16            Paul-17        1250
17            Rita-99        1300
19            Rita-99        1300

期望的结果

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
84            reverse     Paul-17       1250
90            reverse     Rita-99       1300

在第一个查询中，“kaka-19”有 5 个重复项。在第二个查询中，相同的“kaka-19”有 4 个重复项。我希望我的结果如上所示，其中 (5-4)+1 = 2 表示“kaka-19”等等。

注意。查询 2 中的注释存储应该是唯一的发票编号。如果 OINV 表中任意两条注释相同，则表示存在重复错误。ORIN 表中的 JrnlMemo 字段存储 OINV 发票编号。对于任何发票重复没有。k，要冲销的贷方票据的正确数量应该是 k - 1。

如何为所需结果构建查询？