sql-server - 如何找到包括当前金额在内的一年的先前金额的总和
问题描述
我需要获得第二个条目的当前金额与该特定年份的先前金额的总和。
输入表
+-----------+----------+-------------+-----------+----------+
| ID | Name | Date1 |Date 2 |amount |
+-----------+----------+-------------+-----------+----------+
| 10000000 | ABC | 11/2/2017 |**11/2/2018** |2504 |
| 10000000 | ABC | 12/20/2017 |**11/2/2018** |-2174 |
| 10000000 | ABC | 10/05/2018 |10/05/2019 |1234 |
| 10000000 | ABC | 10/06/2019 |10/06/2020 |3456 |
+-----------+----------+-------------+-----------+----------+
这是所需的输出:
+-----------+----------+-------------+-----------+----------+
| ID | Name | Date1 |Date 2 |amount |
+-----------+----------+-------------+-----------+----------+
| 10000000 | ABC | 11/2/2017 |**11/2/2018** |2504 |
| 10000000 | ABC | 12/20/2017 |**11/2/2018** |329 |
| 10000000 | ABC | 10/05/2018 |10/05/2019 |1234 |
| 10000000 | ABC | 10/06/2019 |10/06/2020 |3456 |
+-----------+----------+-------------+-----------+----------+
记录 3 金额 - 1234(1234+0(当年没有记录)),因为日期 2 不同
记录 4 金额 - 3456(3456+0(当年无记录)),因为日期 2 不同
如果两条记录的日期 2 相同,我正在寻找记录的当前、先前金额值的总和。假设我们有 3 条具有相同日期 2 值的记录。那么第一个记录应该有它的实际金额值,第二个记录应该有第一个记录金额+ 2个记录金额,3个记录应该有第一个记录金额+2个记录金额+3个记录金额。
解决方案
我在这里做了一些相当大的假设。主要是,您想查看当前记录的“Date2”值是否存在于上一年的“Date1”中(例如 Date2: 11/02/2018 正在查看 Date1: 11/02/2017 是否存在)以及ID和名称列在过滤数据时具有某种相关性。
这是我在 SSMS 中使用的内容:
-- Create sample data.
DECLARE @Data TABLE (
ID VARCHAR(10), [Name] VARCHAR(3), Date1 DATE, Date2 DATE, Amount DECIMAL(18,2)
);
INSERT INTO @Data ( ID, [Name], Date1, Date2, Amount ) VALUES
( '10000000', 'ABC', '11/2/2017', '11/2/2018', 2504),
( '10000000', 'ABC', '12/20/2017', '11/2/2018', -2174),
( '10000000', 'ABC', '10/05/2018', '10/05/2019', 1234),
( '10000000', 'ABC', '10/06/2019', '10/06/2020', 3456);
-- Query sample data
SELECT
MyData.ID, MyData.[Name], MyData.Date1, MyData.Date2, MyData.Amount
, ISNULL( LastYear.Amount, 0 ) AS PreviousYear
, ( MyData.Amount + ISNULL( LastYear.Amount, 0 ) ) AS NewAmount
FROM @Data AS MyData
OUTER APPLY (
SELECT
SubData.Amount AS Amount
FROM @Data AS SubData
WHERE
SubData.ID = MyData.ID
AND SubData.[Name] = MyData.[Name]
AND SubData.Date1 = DATEADD( yy, -1, MyData.Date2 )
AND SubData.Date1 <> MyData.Date1 -- A weak attempt to exclude the current MyData record. Really needs a unique id.
) AS LastYear
ORDER BY
MyData.Date2;
-- 退货
+----------+------+------------+------------+----------+--------------+-----------+
| ID | Name | Date1 | Date2 | Amount | PreviousYear | NewAmount |
+----------+------+------------+------------+----------+--------------+-----------+
| 10000000 | ABC | 2017-11-02 | 2018-11-02 | 2504.00 | 0.00 | 2504.00 |
| 10000000 | ABC | 2017-12-20 | 2018-11-02 | -2174.00 | 2504.00 | 330.00 |
| 10000000 | ABC | 2018-10-05 | 2019-10-05 | 1234.00 | 0.00 | 1234.00 |
| 10000000 | ABC | 2019-10-06 | 2020-10-06 | 3456.00 | 0.00 | 3456.00 |
+----------+------+------------+------------+----------+--------------+-----------+
理想情况下,查询表有一个 PK,可用于排除当前记录被包含(注意使用 Date1 的弱尝试)。
使用 PK(唯一 ID)的相同示例:
DECLARE @Data TABLE (
ID VARCHAR(10), [Name] VARCHAR(3), Date1 DATE, Date2 DATE, Amount DECIMAL(18,2), pk_PrimaryKey INT IDENTITY(1,1) PRIMARY KEY
);
INSERT INTO @Data ( ID, [Name], Date1, Date2, Amount ) VALUES
( '10000000', 'ABC', '11/2/2017', '11/2/2018', 2504),
( '10000000', 'ABC', '12/20/2017', '11/2/2018', -2174),
( '10000000', 'ABC', '10/05/2018', '10/05/2019', 1234),
( '10000000', 'ABC', '10/06/2019', '10/06/2020', 3456);
SELECT
MyData.ID, MyData.[Name], MyData.Date1, MyData.Date2, MyData.Amount
, ISNULL( LastYear.Amount, 0 ) AS PreviousYear
, ( MyData.Amount + ISNULL( LastYear.Amount, 0 ) ) AS NewAmount
FROM @Data AS MyData
OUTER APPLY (
SELECT
SubData.Amount AS Amount
FROM @Data AS SubData
WHERE
SubData.ID = MyData.ID
AND SubData.[Name] = MyData.[Name]
AND SubData.Date1 = DATEADD( yy, -1, MyData.Date2 )
AND SubData.pk_PrimaryKey <> MyData.pk_PrimaryKey
) AS LastYear
ORDER BY
MyData.Date1;
无论如何,这是我最好的猜测,基于我必须继续的少量信息。希望这可以使您朝着正确的方向前进。
推荐阅读
- javascript - Phaser - 在创建函数之外使用 getChildByName 访问 DOM 元素
- charts - 如何将文本添加到 Chart.js 中的点?
- vue.js - 根据下拉选择显示/隐藏下一个输入字段
- django - 如何将 UUIDField 用于 SQLite?
- reactjs - 为什么 Jest 单元测试调试不会在 Chrome 检查器的断点处停止?
- spring-boot - 为什么在尝试保存包含枚举的实体时出现错误?
- python - 试图将循环字符串添加到空数据框熊猫
- python - Matplotlib 标签问题
- c# - WCF 401“远程服务器未经授权返回错误”
- python - 从 Google 导入 Create_Service ModuleNotFoundError:没有名为“Google”的模块