CODEWITHSUNDEEP
selectreporting-servicessql-server-2012
Joe Resler
Joe Resler

Reputation: 101

SELECT previous year data based on year column

I am trying to develop a SSRS report. I have taken sales values from a transactional table and grouped them by year, month and subcategory. I have created this as a view. Here is the view code:

SELECT b.FiscalYear AS Year, 
       b.FiscalMonth AS Month, 
       a.SubCategoryKey, 
       MAX(a.SubCategoryDesc) AS SubCategoryDesc, 
       SUM(CAST(a.Cost + a.FreightCost AS decimal(18, 2))) AS TotalCost, 
       SUM(CAST(a.SalesAmount AS decimal(18, 2))) AS   TotalSales,       
SUM(CAST(a.Weight AS decimal(18, 2))) AS Pounds, SUM(CAST(a.SalesAmount -(a.Cost + a.FreightCost) AS decimal(18, 2))) AS Margin
FROM dbo.GrossMargin_CorrectedCosts AS a 
LEFT OUTER JOIN dbo.M_DateDim AS b ON a.InvoiceDate = b.Date
GROUP BY b.FiscalYear, b.FiscalMonth, a.SubCategoryKey

I am then left with a clean view that looks like this:

SELECT [Year]
  ,[Month]
  ,[SubCategoryKey]
  ,[SubCategoryDesc]
  ,[TotalCost]
  ,[TotalSales]
  ,[Pounds]
  ,[Margin] 
  FROM [FinancialData].[dbo].[SubCategorySalesbyMonth_V]\

I'd now like to add additonal columns to this query. I am going to run this as a SSRS report and pass both Year and Month parameters. What I'd like to do is when 2017 is selected as the passed Year parameters, then I would like to show previous year values for TotalSales, TotalCost and Pounds.

Whereas the query would look something like this:

SELECT [Year]
  ,[Month]
  ,[SubCategoryKey]
  ,[SubCategoryDesc]
  ,[TotalCost]
  ,[TotalSales]
  ,[Pounds]
  ,[Margin]
  ,PreviousYearTotalSales
  ,PreviousYearTotalCost
  ,PreviousYearPounds 
  FROM [FinancialData].[dbo].[SubCategorySalesbyMonth_V]

Essentially when a year and month is passed to the report, I'd like to show the previous years totalsales, totalcost and pounds for that given period minus one year. I am having a heck of time doing this.

I feel like I have tried everything but obviously not. Please help.

Upvotes: 1

Views: 928

Answers (1)

R. Richards
R. Richards

Reputation: 25161

Something that could work now that you have a view to work with is the following query.

SELECT CY.[Year]
,CY.[Month]
,CY.[SubCategoryKey]
,CY.[SubCategoryDesc]
,CY.[TotalCost]
,CY.[TotalSales]
,CY.[Pounds]
,CY.[Margin] 
,PY.[TotalSales] AS PreviousYearTotalSales
,PY.[TotalCost] AS PreviousYearTotalCost
,PY.[Pounds] AS PreviousYearPounds 
FROM [FinancialData].[dbo].[SubCategorySalesbyMonth_V] CY LEFT JOIN [FinancialData].[dbo].[SubCategorySalesbyMonth_V] PY
ON CY.[Year] - 1 = PY.[Year]
AND CY.[Month] = PY.[Month]
AND CY.[SubCategoryKey] = PY.[SubCategoryKey]
AND CY.[Month] = PY.[Month]

This is just a simple self left join to the same view, but the year from the current year (CY) is joining back to the prior year (PY), year - 1. It is a LEFT JOIN, so the prior year's values will be null if there isn't any prior year/month's data available.

Hope this helps.

Upvotes: 2

Related Questions