SQL 查询汇总年初至今的实际值和 YTD 的平均值

Question

我需要一个查询，总计费用实际 YTD、平均 YTD(在所有年份中，我的平均年份是多少)、去年 YTD

我需要 3 个数据:实际 YTD |年初至今平均 |去年|

这是我目前所拥有的:

select  SUM(Expenses)
        ,DATEADD(year, datediff(year,0,[Date]), 0) 
from    table1 
group by  
        DATEADD(year, datediff(year,0,[Date]), 0) 
) 

Answer 1

试试这个:

DECLARE @currentYear int
SELECT @currentYear = DATEPART(year, GETDATE())

SELECT SUM(CASE WHEN DATEPART(year,[date]) = @currentYear 
    THEN Expenses ELSE 0 END) AS 'Actual YTD',
SUM(Expenses) / COUNT( DISTINCT DATEPART(year,[date])) AS 'Average YTD',
SUM(CASE WHEN DATEPART(year,[date]) = @currentYear -1 
    THEN Expenses ELSE 0 END) AS 'Last Year YTD'

FROM Table1

SQL Fiddle