How to write SQL query for monthly revenue comparison between two years

Data and Databases
1

I’m working with a database view that contains monthly sales data and I need help creating a comparison report. Here’s what my data structure looks like:

Product_Type | Sales_Amount | Year_Val | Month_Val
Laptops        8500          2019       1
Laptops        9200          2019       2
Laptops        9800          2019       3
Laptops        7300          2018       1
Laptops        8100          2018       2
Laptops        8900          2018       3
Laptops        9650          2018       4

I want to build a report that compares sales between 2018 and 2019. The tricky part is that I need to show zero values for months where there were no sales in one year but sales in the other. My desired output should be:

Product_Type | Month_Val | Current_Year_Sales | Previous_Year_Sales
Laptops         1           8500                 7300
Laptops         2           9200                 8100
Laptops         3           9800                 8900
Laptops         4           0                    9650

I tried using a LEFT JOIN but it only shows records where the current year has data. How can I modify my query to include months that only exist in the previous year? I’m using SQL Server 2005 and would appreciate any suggestions for the best approach to solve this problem.

2

use FULL OUTER JOIN instead of LEFT JOIN - it’ll pull records from both tables even when there’s no match. try this: SELECT p.Product_Type, COALESCE(c.Month_Val, p.Month_Val), ISNULL(c.Sales_Amount,0), ISNULL(p.Sales_Amount,0) FROM table c FULL OUTER JOIN table p ON c.Product_Type=p.Product_Type AND c.Month_Val=p.Month_Val WHERE c.Year_Val=2019 AND p.Year_Val=2018. perfect for comparing datasets.

3

Interesting challenge! What if you pivot the years into columns first, then unpivot the months? Does your SQL Server version support PIVOT/UNPIVOT? Might be cleaner than multiple joins. Also, do you need specific product types or all products for the comparison?

4

Your LEFT JOIN only keeps records from the left table, but you need all month combinations for both years. First, create a complete set of months that exist across both years, then join your sales data to that.

Create a CTE or subquery that finds all unique months present in either year for each product. Use this as your base and LEFT JOIN your actual sales data twice - once for each year. This way every month with sales in either year shows up in your results.

Here’s the basic approach: WITH all_months AS (SELECT DISTINCT Product_Type, Month_Val FROM your_table WHERE Year_Val IN (2018, 2019)) then join this CTE with your sales data filtered by year. Use ISNULL or COALESCE to turn NULL values into zeros for months without sales. This guarantees you’ll get all months regardless of which year they appear in.