sql - 如何在 SQL 查询中添加年份参数？

Question

我正在使用水晶报表创建一个报告来比较两年的销售额。我需要添加年份参数来过滤数据，方法是在 CurrentYear 参数和 PreviousYear 参数中输入年份。我该怎么做？

我尝试了下面的查询，但问题是如何将参数添加到聚合函数？

DECLARE @CompareYear varchar(50),
@CurrentYear varchar(50)

SELECT 
    ISNULL(sp.Telephone,'No Sales Employee'),
    od.CardCode,
    od.CardName,
    Sum(case when Year(od.DocDate)=@CompareYear then od.DocTotal else 0 end) as Sold2018,
    Sum(case when Year(od.DocDate)=@CurrentYear then od.DocTotal else 0 end) as Sold2019
FROM
    ORDR od
Left Join OSLP sp
    On od.SlpCode=sp.SlpCode
Group By
    sp.Telephone,
    od.CardCode,
    od.CardName
having(Sum(case when Year(od.DocDate)=@CompareYear then od.DocTotal else 0 end) < Sum(case when Year(od.DocDate)=@CurrentYear then od.DocTotal else 0 end))

标签： sqlparameterssql-server-2014

Answer 1

如果我正确地抓住了你的目标，你需要同时添加YEAR(od.DocDate)onGROUP BY和SELECT从句。