我有这张表和样本数据，我想计算期初和期末余额。我希望它在 6 列中
即 CreditOpening , DebitOpening , Credit , Debit , CreditClosing , DebitClosing
这是表结构和示例数据
删除表事务；
CREATE TABLE Transactions
(
ID INT,
COATitle VARCHAR(35),
ConfigurationCode INT,
DebitAmount NUMERIC,
CreditAmount NUMERIC,
TransactionDate Date
)
INSERT INTO Transactions VALUES (1, 'Sales', 24, '2400', NULL, '2018-08-24');
INSERT INTO Transactions VALUES (2, 'Items', 24, NULL, '1200', '2018-08-24');
INSERT INTO Transactions VALUES (3, 'Bank', 24, NULL, '1200', '2018-08-24');
INSERT INTO Transactions VALUES (4, 'Meezan', 24, '1500', NULL, '2018-08-25');
INSERT INTO Transactions VALUES (5, 'Items', 24, NULL, '1500', '2018-08-25');
INSERT INTO Transactions VALUES (6, 'Bank', 24, NULL, '1200', '2018-08-26');
INSERT INTO Transactions VALUES (7, 'Sales', 24, '5400', NULL, '2018-08-26');
INSERT INTO Transactions VALUES (8, 'Items', 24, NULL, '1200', '2018-08-26');
INSERT INTO Transactions VALUES (9, 'Bank', 24, NULL, '3000', '2018-08-26');
我有这个查询，它的输出如下:
;WITH CTE AS (
SELECT *
FROM
Transactions
)
SELECT
COATitle, SUM([D].[DebitAmount]) DrAmount, SUM([D].[CreditAmount]) CrAmount
FROM(
SELECT *,
SUM(ISNULL(NULLIF(DebitAmount, 0), 0)-ISNULL(NULLIF(CreditAmount, 0), 0)) OVER (PARTITION BY CONFIGURATIONCODE ORDER BY ID) as Amount
FROM CTE
WHERE [TransactionDate] BETWEEN CAST('Aug 25 2018 11:21AM' AS DATE) AND CAST('Aug 25 2018 11:21AM' AS DATE)
)D
GROUP BY COATitle
输出 :
COATitle DrAmount CrAmount
Items NULL 1500
Meezan 1500 NULL
现在数据应该是这样的
COATitle OpeningDebit OpeningDebit DrAmount CrAmount ClosingDebit ClosingCredit
Bank 0 0 NULL 1200 0 1200
Items 0 0 NULL 1200 0 1200
Sales 0 0 2400 NULL 2400 0
但是一旦我在日期 25 和 26 之间运行查询，结果应该是这样的
COATitle OpeningDebit OpeningCredit DrAmount CrAmount ClosingDebit ClosingCredit
Bank 0 1200 NULL 4200 0 5400
Items 0 1200 NULL 2700 0 3900
Sales 0 0 5400 NULL 7800 0
Meezan 0 0 1500 NULL 1500 0
Meezan 将没有期初余额，因为在前一天没有。现在，如果任何 COATTitle 在以前的日期提供贷方，如果有借方金额，它将从借方中减去贷方。
最佳答案
在这种情况下，您不需要 CTE。这是多余的。
您不需要将字符串中的日期转换为日期数据类型。只需以 ISO 格式 YYYY-MM-DD 指定日期即可。
我可能是错的，但您的预期数据与样本数据不匹配。
注意:我在查询中排除了 ConfigurationCode，因为我不确定该列如何在您的要求中发挥作用。
DECLARE @Date_Fr DATE = '2018-08-25',
@Date_To DATE = '2018-08-25'
SELECT t.COATitle,
OpeningDebit = ISNULL(o.OpeningDebit, 0),
OpeningCredit = ISNULL(o.OpeningCredit, 0),
t.DrAmount, t.CrAmount,
ClosingDebit = ISNULL(o.OpeningDebit, 0) + t.DrAmount,
ClosingCredit = ISNULL(o.OpeningCredit, 0) + t.CrAmount
FROM (
SELECT COATitle,
DrAmount = SUM(CASE WHEN [TransactionDate] >= @Date_Fr THEN DebitAmount ELSE 0 END),
CrAmount = SUM(CASE WHEN [TransactionDate] >= @Date_Fr THEN CreditAmount ELSE 0 END)
FROM Transactions t
WHERE [TransactionDate] <= @Date_To
GROUP BY COATitle
) t
OUTER APPLY
(
SELECT OpeningDebit = SUM(DebitAmount), OpeningCredit = SUM(CreditAmount)
FROM Transactions x
WHERE x.COATitle = t.COATitle
AND x.[TransactionDate] < @Date_Fr
) o
WHERE o.OpeningDebit IS NOT NULL
OR o.OpeningCredit iS NOT NULL
关于sql - 期初和期末余额总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52056941/