I guess you wanted both the result to appear side by side ?
You can use conditional case statement to do that
DECLARE @fdate as date
DECLARE @secdate as date
SET @fdate = '20200101';
SET @secdate = GETDATE();
SELECT
trans.STORE As 'Store Number' ,
SUM (CASE WHEN trans.TRANSDATE BETWEEN @fdate
AND @secdate
THEN trans.NETAMOUNT * -1 END) AS sales2019,
SUM (CASE WHEN trans.TRANSDATE BETWEEN DATEADD(YEAR, -1, @fdate)
AND DATEADD(YEAR, -1, @secdate)
THEN trans.NETAMOUNT * -1 END) AS sales2018
FROM
RETAILTRANSACTIONSALESTRANS AS trans
WHERE
trans.TRANSDATE BETWEEN @fdate
AND @secdate
OR trans.TRANSDATE BETWEEN DATEADD(YEAR, -1, @fdate)
AND DATEADD(YEAR, -1, @secdate)
GROUP BY
trans.STORE
Note : You don't have to convert GETDATE()
to string before assigning to @secdate
, since @secdate
is a date
data type.
Also is is best to specify the date string literal in YYYYMMDD format
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…