sql - 我想在 ssrs 上显示当前月份、年初至今和上一年至今

Question

数据展示

想在 sql 视图上计算年初至今和上一年至今当月，以便在 ssrs 上简单地显示数据以使其运行得更快。有没有办法编写一个可以执行此操作的视图？

标签： sqlreporting-services

Answer 1

忽略我认为您在以前的 YTD 汇总数字中有一些错误的事实。

我根据您的示例重新创建了数据

CREATE TABLE #t (TransDate date, Customer varchar(30), Amount float)

INSERT INTO #t VALUES
('2020-09-21', 'Customer 1', 200),
('2020-09-22', 'Customer 2', 300),
('2020-08-03', 'Customer 2', 450),
('2020-08-04', 'Customer 1', 1200),
('2019-09-14', 'Customer 1', 859),
('2019-02-05', 'Customer 2', 230),
('2019-07-26', 'Customer 2', 910),
('2019-11-17', 'Customer 1', 820)

那么下面的语句就会产生你所需要的。这不是最优雅的方法，但它会很容易地转换为视图，并且是我能想到的。

SELECT 
    m.Customer
    , m.MTD as [Current Month]
    , y.YTD as [Current YTD]
    , p.YTD as [Previous YTD]
FROM (
        SELECT Customer, Yr = Year(TransDate), Mn = MONTH(TransDate), MTD = SUM(Amount) FROM #t t WHERE MONTH(TransDate) = MONTH(GetDate()) and YEAR(TransDate) = YEAR(GetDate())
            GROUP by Customer, YEAR(TransDate), MONTH(TransDate)
    ) m 
    JOIN (SELECT Customer, Yr = YEAR(TransDate), YTD = SUM(Amount) FROM #t t  GROUP by Customer,  YEAR(TransDate)) y on m.Customer =y.Customer and m.Yr = y.Yr
    JOIN (SELECT Customer, Yr = YEAR(TransDate), YTD = SUM(Amount) FROM #t t  GROUP by Customer,  YEAR(TransDate)) p on y.Customer =p.Customer and y.Yr = p.Yr + 1

这给出了以下结果（与您的示例不匹配，但我认为您的示例不正确）