SQL query to compare product sales by month

Question

I have a view of the Monthly Status database. I need to create a report based on. The data in the view looks something like this:

Category | Revenue | Yearh | Month Bikes 10 000 2008 1 Bikes 12 000 2008 2 Bikes 12 000 2008 3 Bikes 15 000 2008 1 Bikes 11 000 2007 2 Bikes 11 500 2007 3 Bikes 15 400 2007 4 

... And so on

The view has a product category, income, year and month. I want to create a report compared to 2007 and 2008, showing 0 for months without sales. Therefore, the report should look something like this:

 Category | Month | Rev. This Year | Rev. Last Year Bikes 1 10 000 0 Bikes 2 12 000 11 000 Bikes 3 12 000 11 500 Bikes 4 0 15 400 

The key point that should be noted is that month 1 has only sales in 2008 and therefore is 0 for 2007. In addition, the 4th month has no sales in 2008, therefore 0, while it has sales in 2007 and is still displayed.

In addition, the report is actually for the fiscal year - therefore, I would like the column “empty” to be 0, if there were no sales in the 5th month in 2007 or 2008.

The received request looks something like this:

 SELECT SP1.Program, SP1.Year, SP1.Month, SP1.TotalRevenue, IsNull(SP2.TotalRevenue, 0) AS LastYearTotalRevenue FROM PVMonthlyStatusReport AS SP1 LEFT OUTER JOIN PVMonthlyStatusReport AS SP2 ON SP1.Program = SP2.Program AND SP2.Year = SP1.Year - 1 AND SP1.Month = SP2.Month WHERE SP1.Program = 'Bikes' AND SP1.Category = @Category AND (SP1.Year >= @FinancialYear AND SP1.Year <= @FinancialYear + 1) AND ((SP1.Year = @FinancialYear AND SP1.Month > 6) OR (SP1.Year = @FinancialYear + 1 AND SP1.Month <= 6)) ORDER BY SP1.Year, SP1.Month 

The problem with this query is that it will not return the fourth row in the examples above, since in 2008 we had no sales, but we really did it in 2007.

This is probably a common query / problem, but my SQL code is rusty after a long development. Any help is much appreciated!

Oh, by the way, I'm using SQL 2005 for this query, so if there are useful useful features that can help me tell me.

Answer 1

Case is my best friend in sql. You also need a time table to generate 0 revolutions in both months.

Assumptions are based on the availability of the following tables:

sales: Category | Income | Years | Month

and

tm: Year | Month (filled with all the dates required for reporting)

Example 1 without blank lines:

 select Category ,month ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year from sales where year in (2008,2007) group by Category ,month 

RETURN:

 Category | Month | Rev. This Year | Rev. Last Year Bikes 1 10 000 0 Bikes 2 12 000 11 000 Bikes 3 12 000 11 500 Bikes 4 0 15 400 

Example 2 with empty strings: I am going to use an additional query (but others may not) and will return an empty string for each combo product and year.

 select fill.Category ,fill.month ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year from sales Right join (select distinct --try out left, right and cross joins to test results. product ,year ,month from sales --this ideally would be from a products table cross join tm where year in (2008,2007)) fill where fill.year in (2008,2007) group by fill.Category ,fill.month 

RETURN:

 Category | Month | Rev. This Year | Rev. Last Year Bikes 1 10 000 0 Bikes 2 12 000 11 000 Bikes 3 12 000 11 500 Bikes 4 0 15 400 Bikes 5 0 0 Bikes 6 0 0 Bikes 7 0 0 Bikes 8 0 0 

Note that most reporting tools will run this crosstab or matrix functionality, and now that I think about it, SQL Server 2005 has syntax that will also do this.

Here are some additional resources. CASE http://www.4guysfromrolla.com/webtech/102704-1.shtml SQL SERVER 2005 PIVOT http://msdn.microsoft.com/en-us/library/ms177410.aspx

Answer 2

@Christian - markdown editor - UGH; especially when the preview and the final version of your message are not consistent ... @Christian - full outer join - the full outer join is canceled by the fact that there are references to SP1 with respect to WHERE and the WHERE clause is applied after JOIN. To perform a full outer join with filtering in one of the tables, you need to put the WHERE clause in the subquery, so filtering happens before joining or tries to build all your WHERE criteria in the JOIN ON clause, which is insanely ugly. Well, there really is no nice way to do this.

@Jonas: Given this:

In addition, the report is valid for the fiscal year - so I would like to have empty columns with 0 in both if there were no sales in the 5th month in 2007 or 2008.

and the fact that this work cannot be done with a pretty query, I will definitely try to get the results you need. It makes no sense to have an ugly request and not even get the exact data that you really want.;)

So, I would suggest doing this in 5 steps:
1. Create a temporary table in a format that matches your results. 2. Fill it with twelve rows, from 1-12 in the month column
3. Update the “This Year” column using SP1 logic
4. Update the Last Year column using SP2 logic
5. select a pace from the table

Of course, I suggest that I work on the assumption that you can create a stored procedure for this. You may technically be able to run this entire package, but such ugliness is very rare. If you can’t do the SP, I suggest you return to the full external join via a subquery, but it won’t give you a row if the month hasn’t sold a year.

Answer 3

About markdown - Yes, it is frustrating. The editor looked at my HTML table, but after publication it disappeared - so I had to remove all the HTML formatting from the message ...

@kcrumley I think we have come to similar conclusions. This query easily becomes really ugly. I really solved this before reading your answer using a similar (but still different approach). I have access to create stored procedures and functions in a reporting database. I created a Table Valued function that takes a product category and a fiscal year as a parameter. Based on this, the function will populate a table containing 12 rows. The rows will be filled with data from the view, if any sales are available, if not the row will have 0 values.

Then I join the two tables returned by the functions. Since I know that all tables will have twelve veils, they will be easier, and I can join the product category and months:

 SELECT SP1.Program, SP1.Year, SP1.Month, SP1.TotalRevenue AS ThisYearRevenue, SP2.TotalRevenue AS LastYearRevenue FROM GetFinancialYear(@Category, 'First Look', 2008) AS SP1 RIGHT JOIN GetFinancialYear(@Category, 'First Look', 2007) AS SP2 ON SP1.Program = SP2.Program AND SP1.Month = SP2.Month 

I think your approach is probably a little cleaner since the GetFinancialYear function is pretty dirty! But at least it works - which makes me happy at the moment;)

Answer 4

The trick is to make a FULL JOIN, with ISNULL, to get the joined columns from any table. I usually wrap this in a table of views or derivatives, otherwise you need to use ISNULL in the WHERE clause.

 SELECT Program, Month, ThisYearTotalRevenue, PriorYearTotalRevenue FROM ( SELECT ISNULL(ThisYear.Program, PriorYear.Program) as Program, ISNULL(ThisYear.Month, PriorYear.Month), ISNULL(ThisYear.TotalRevenue, 0) as ThisYearTotalRevenue, ISNULL(PriorYear.TotalRevenue, 0) as PriorYearTotalRevenue FROM ( SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue FROM PVMonthlyStatusReport WHERE Year = @FinancialYear GROUP BY Program, Month ) as ThisYear FULL OUTER JOIN ( SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue FROM PVMonthlyStatusReport WHERE Year = (@FinancialYear - 1) GROUP BY Program, Month ) as PriorYear ON ThisYear.Program = PriorYear.Program AND ThisYear.Month = PriorYear.Month ) as Revenue WHERE Program = 'Bikes' ORDER BY Month 

This should give you the minimum requirements - sales lines in 2007 or 2008, or both. To get rows without sales within one year, you just need to enter INNER JOIN in the table with numbers 1-12 (you have one of them , right?).

Answer 5

I could be wrong, but shouldn't you use a full outer join, and not just a left join? This way you get the "empty" columns from both tables.

http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join

Answer 6

Using pivot and Dynamic Sql, we can achieve this result.

 SET NOCOUNT ON IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL DROP TABLE #TEMP ;With cte(Category , Revenue , Yearh , [Month]) AS ( SELECT 'Bikes', 10000, 2008,1 UNION ALL SELECT 'Bikes', 12000, 2008,2 UNION ALL SELECT 'Bikes', 12000, 2008,3 UNION ALL SELECT 'Bikes', 15000, 2008,1 UNION ALL SELECT 'Bikes', 11000, 2007,2 UNION ALL SELECT 'Bikes', 11500, 2007,3 UNION ALL SELECT 'Bikes', 15400, 2007,4 ) SELECT * INTO #Temp FROM cte Declare @Column nvarchar(max), @Column2 nvarchar(max), @Sql nvarchar(max) SELECT @Column=STUFF((SELECT DISTINCT ','+ 'ISNULL('+QUOTENAME(CAST(Yearh AS VArchar(10)))+','+'''0'''+')'+ 'AS '+ QUOTENAME(CAST(Yearh AS VArchar(10))) FROM #Temp order by 1 desc FOR XML PATH ('')),1,1,'') SELECT @Column2=STUFF((SELECT DISTINCT ','+ QUOTENAME(CAST(Yearh AS VArchar(10))) FROM #Temp FOR XML PATH ('')),1,1,'') SET @Sql= N'SELECT Category,[Month],'+ @Column +'FRom #Temp PIVOT (MIN(Revenue) FOR yearh IN (' +@Column2 +') ) AS Pvt ' EXEC(@Sql) Print @Sql 

Result

 Category Month 2008 2007 ---------------------------------- Bikes 1 10000 0 Bikes 2 12000 11000 Bikes 3 12000 11500 Bikes 4 0 15400