查询中的 SQL Server 逻辑问题

SQL Server logical issue in query

提问人：Sumeet Kumar 提问时间：9/29/2023 最后编辑：marc_sSumeet Kumar 更新时间：10/2/2023 访问量：84

问：

我有一个值为 2019 年至 2023 年的年硕士。对于每年，我都创建了2019年至2019年，2019年至2020年，2019年至2021年，2019年至2022年，2019年至2023年的范围。因为我想根据项目每年添加到明年。NetValueenddate

  1. 我想对每个项目进行检查，就好像任何项目从 2019 年开始到 2019 年结束，然后应该只添加那一年。SUM()NetValue
  2. 如果项目在2019年开始，但在2022年结束，那么它的净值应该包括2019年、2021年和2022年。
  3. 如果项目于 2019 年开始并且为 null，则意味着它一直持续到最大年份值。那么它的净值应该在2019年、2020年、2021年、2022年、2023年增加。enddate

请提供一些想法来实现此逻辑。到目前为止，我尝试过的如下：

;WITH YearRanges AS 
(
    SELECT 2019 AS StartYear, 2019 AS EndYear
    UNION ALL
    SELECT StartYear, EndYear + 1
    FROM YearRanges
    WHERE EndYear + 1 <= 2023
)
SELECT
    YR.EndYear AS Year,
    SUM(CASE
        WHEN YEAR(T.projectstartdate) = YR.EndYear THEN cast(T.NetValue as decimal(18,2))
        WHEN YEAR(T.enddate) >= YR.EndYear THEN cast(T.NetValue as decimal(18,2))
        ELSE 0
    END) AS TotalAmount
FROM 
    YearRanges YR
JOIN 
    TrackerMainEntry T ON YEAR(T.projectstartdate) >= YR.StartYear
GROUP BY 
    YR.EndYear
OPTION (MAXRECURSION 0);

试：我尝试根据指定的范围进行选择。

导致：我每年都会得到错误的值。我也申请了，但仍然给了我错误的值。Cross Join

以下是我实际必须实现的逻辑

SELECT
    NetValue, projectstartdate, enddate 
FROM  
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2019) 

UNION

SELECT
    NetValue, projectstartdate, enddate 
FROM
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2019) 
    AND YEAR(enddate) IS NULL

UNION

SELECT
    NetValue, projectstartdate, enddate 
FROM
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2019) 
    AND YEAR(enddate) = 2022

UNION

SELECT
    NetValue, projectstartdate, enddate 
FROM
    [TrackerMainEntry] 
WHERE
    YEAR(projectstartdate) IN (2020)

示例数据：

CREATE TABLE [dbo].[SampleData]
(
    [productname] [varchar](100) NULL,
    [NetValue] [varchar](100) NULL,
    [ProjectStartDate] [date] NULL,
    [EndDate] [date] NULL);
GO

INSERT [dbo].[SampleData] ([productname], [NetValue], [ProjectStartDate], [EndDate])
VALUES ('Project_22', '-11224.68', CAST('2020-03-01' AS Date), CAST('2021-12-15' AS Date)),
       ('Project_64', '261706.4', CAST('2019-11-01' AS Date), CAST('2022-08-18' AS Date)),
       ('Project_64', '21309.44', CAST('2021-01-01' AS Date), CAST('2022-08-18' AS Date)),
       ('Project_4', '3057.2', CAST('2020-03-01' AS Date), NULL),
       ('Project_39', '88298.272', CAST('2020-07-01' AS Date), CAST('2022-08-08' AS Date)),
       ('Project_33', '256230.16', CAST('2019-12-01' AS Date), CAST('2022-08-30' AS Date)),
       ('Project_10', '219442.44', CAST('2021-10-01' AS Date), CAST('2021-11-26' AS Date)),
       ('Project_61', '-18707.8', CAST('2021-06-01' AS Date), NULL),
       ('Project_44', '40444.52', CAST('2021-10-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_37', '989082', CAST('2021-11-01' AS Date), CAST('2021-12-15' AS Date)),
       ('Project_62', '113845.344', CAST('2019-01-01' AS Date), NULL),
       ('Project_63', '143278.56', CAST('2021-05-01' AS Date), CAST('2022-09-15' AS Date)),
       ('Project_68', '33998.896', CAST('2021-05-01' AS Date), CAST('2022-08-01' AS Date)),
       ('Project_65', '56889.04', CAST('2020-04-01' AS Date), NULL),
       ('Project_56', '279507.92', CAST('2020-10-01' AS Date), NULL),
       ('Project_20', '145405.92', CAST('2022-05-01' AS Date), CAST('2022-05-27' AS Date)),
       ('Project_60', '365556.16', CAST('2022-08-22' AS Date), CAST('2022-05-27' AS Date)),
       ('Project_5', '5322.264', CAST('2020-08-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_51', '31690.9', CAST('2020-12-01' AS Date), NULL),
       ('Project_67', '28117.984', CAST('2021-06-01' AS Date), NULL),
       ('Project_59', '10735.488', CAST('2021-03-01' AS Date), NULL),
       ('Project_12', '2974.98', CAST('2022-05-03' AS Date), CAST('2022-05-13' AS Date)),
       ('Project_29', '18307.36', CAST('2019-09-01' AS Date), NULL),
       ('Project_47', '147818.38', CAST('2020-09-01' AS Date), NULL),
       ('Project_2', '-8660.24', CAST('2021-01-01' AS Date), CAST('2021-12-15' AS Date)),
       ('Project_16', '14490.552', CAST('2020-10-01' AS Date), NULL),
       ('Project_45', '188519.088', CAST('2021-03-01' AS Date), NULL),
       ('Project_15', '161817.76', CAST('2021-02-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_55', '39743.344', CAST('2022-01-01' AS Date), CAST('2022-05-30' AS Date)),
       ('Project_35', '139378.08', CAST('2020-12-01' AS Date), CAST('2022-08-18' AS Date)),
       ('Project_40', '12552.72', CAST('2021-01-01' AS Date), CAST('2023-10-02' AS Date)),
       ('Project_43', '9998.896', CAST('2021-07-01' AS Date), CAST('2023-10-02' AS Date)),
       ('Project_53', '94926.32', CAST('2022-08-01' AS Date), CAST('2022-01-10' AS Date)),
       ('Project_7', '35094.992', CAST('2021-03-01' AS Date), NULL),
       ('Project_50', '20534.18', CAST('2021-05-01' AS Date), CAST('2022-09-01' AS Date)),
       ('Project_8', '674.5', CAST('2020-07-01' AS Date), NULL),
       ('Project_3', '4380.568', CAST('2019-11-01' AS Date), NULL),
       ('Project_10', '42712.64', CAST('2022-09-22' AS Date), CAST('2023-10-02' AS Date)),
       ('Project_33', '129340.44', CAST('2020-10-01' AS Date), NULL),
       ('Project_33', '119190.2', CAST('2021-01-01' AS Date), NULL),
       ('Project_33', '102820.46', CAST('2021-10-01' AS Date), NULL),
       ('Project_33', '150575.48', CAST('2022-09-01' AS Date), NULL),
       ('Project_23', '55964.16', CAST('2020-06-01' AS Date), NULL),
       ('Project_21', '-16.32', CAST('2020-08-01' AS Date), NULL),
       ('Project_6', '-544.66', CAST('2021-02-01' AS Date), NULL),
       ('Project_31', '-411', CAST('2020-08-01' AS Date), NULL),
       ('Project_42', '-378.41', CAST('2021-03-01' AS Date), NULL),
       ('Project_19', '-9460.23', CAST('2020-12-01' AS Date), NULL),
       ('Project_1', '71573.1', CAST('2021-03-01' AS Date), NULL),
       ('Project_26', '282114.4', CAST('2020-12-01' AS Date), NULL),
       ('Project_63', '19964.16', CAST('2022-08-22' AS Date), NULL),
       ('Project_37', '986980', CAST('2022-05-22' AS Date), CAST('2022-09-15' AS Date)),
       ('Project_57', '1349', CAST('2021-07-01' AS Date), NULL),
       ('Project_41', '998.896', CAST('2021-06-01' AS Date), NULL),
       ('Project_17', '12489.04', CAST('2022-08-22' AS Date), NULL),
       ('Project_53', '16853.2', CAST('2022-08-01' AS Date), CAST('2022-10-07' AS Date)),
       ('Project_20', '21003.184', CAST('2022-11-01' AS Date), CAST('2023-02-03' AS Date)),
       ('Project_37', '15302.56', CAST('2022-10-01' AS Date), NULL),
       ('Project_28', '60000', CAST('2023-01-01' AS Date), CAST('2022-10-07' AS Date)),
       ('Project_15', '108967.68', CAST('2022-12-01' AS Date), CAST('2023-02-01' AS Date));
GO
sql-server 选择 日期范围

评论

2赞 Thom A 9/29/2023
耗材样本数据和预期结果将帮助我们为您提供帮助。
0赞 Sumeet Kumar 9/29/2023
@ThomA我正在编辑问题以粘贴示例数据，但无法以正确的格式查看数据。我必须上传文件还是只是粘贴它？
0赞 Thom A 9/29/2023
理想情况下，请为示例数据提供 DDL 和 DML 语句。否则，请使用 Markdown 表。
0赞 Thom A 9/29/2023
您也忘记添加预期结果。
0赞 Sumeet Kumar 9/29/2023
@ThomA我添加了示例数据

答：

1赞 Shane P 10/2/2023 #1

这是你要找的吗？（注意：我稍微修改了你的 CTE......

;WITH YearRanges AS
(
    SELECT 2019 AS [PeriodYear]
    UNION ALL
    SELECT [PeriodYear] + 1
    FROM YearRanges
    WHERE [PeriodYear] + 1 <= 2023
)

SELECT 
    yr.[PeriodYear],
    SUM(sd.NetValue) AS TotalAmount
FROM YearRanges yr
INNER JOIN dbo.SampleData sd
    ON YEAR(sd.ProjectStartDate) <= yr.[PeriodYear]
    AND 
    (
        YEAR(sd.EndDate) IS NULL
        OR YEAR(sd.EndDate) >= yr.[PeriodYear]
    )
GROUP BY yr.[PeriodYear]
ORDER BY yr.[PeriodYear]

/*

Results
-------
PeriodYear  TotalAmount
2019    654469.83
2020    1867903.70
2021    4050471.22
2022    4885286.36
2023    2060527.91

*/

评论

0赞 Sumeet Kumar 10/3/2023
根据我收到的指示，随着时间的推移，它永远不会减少，但它显示的输出在 2023 年正在减少NetValueTotalAmount
0赞 Sumeet Kumar 10/13/2023
了解到 2023 年的项目与往年相比有所减少

上一个：如何查看特定日期范围内的 Facebook 群组帖子

下一个：'pd.date_range（）'如何在不调用'DatetimeIndex'类的任何方法的情况下创建'DatetimeIndex'的对象？