DECLARE @Year INT
SET @Year = 2008
SELECT ISNULL(ROUND(a.SalesAmount,0),0) AS CurrentSales
, ISNULL(ROUND(b.SalesAmount,0),0) As PreviousSales
,a.MONTH As 'Month'
FROM
(SELECT SUM(Fs.SalesAmt) AS SalesAmount
,DD.MonthName AS 'Month'
,DD.MonthNumber
FROM FactSales FS
JOIN DimDate DD on DD.DateKey = FS.DateKey
WHERE DD.Year = @Year
GROUP BY DD.MonthName
,DD.MonthNumber
) AS a
OUTER APPLY
--CROSS APPLY
(SELECT Sum(Fs.SalesAmt) AS SalesAmount
,DD.MONTHNAME AS 'Month'
,DD.MonthNumber
FROM FactSales FS
JOIN DimDate DD ON DD.DateKey = FS.DateKey
WHERE DD.Year = @Year - 1
and DD.MonthNumber = a.MonthNumber
GROUP BY DD.MONTHNAME
,DD.MonthNumber
) AS b
Consider ther is no data for year 2007, then on replacing ‘Outer Apply’ with ‘Cross Apply’ no data will be displayed. But using Outer Apply will display data for the year 2008 (current Sales) and 0 for 2007 (Previous Sales)
No comments:
Post a Comment