<p>I have a Monthly Status database view I need to build a report based on. The data in the view looks something like this:</p>

<pre><code>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
</code></pre>

<p><br/>
... And so forth</p>

<p>The view has a product  category, a revenue, a year and a month. I want to create a report comparing 2007 and 2008, showing 0 for the months with no sales. So the report should look something like this:</p>

<pre><code>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
</code></pre>

<p><br/>
The key thing to notice is how month 1 only has sales in 2008, and therefore is 0 for 2007. Also, month 4 only has no sales in 2008, hence the 0, while it has sales in 2007 and still show up.</p>

<p>Also, the report is actually for financial year - so I would love to have empty columns with 0 in both if there was no sales in say month 5 for either 2007 or 2008.</p>

<p>The query I got looks something like this:</p>

<pre><code>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 &gt;= @FinancialYear AND SP1.Year &lt;= @FinancialYear + 1) AND
    ((SP1.Year = @FinancialYear AND SP1.Month &gt; 6) OR 
     (SP1.Year = @FinancialYear + 1 AND SP1.Month &lt;= 6))

ORDER BY SP1.Year, SP1.Month
</code></pre>

<p>The problem with this query is that it would not return the fourth row in my example data above, since we didn't have any sales in 2008, but we actually  did in 2007.</p>

<p>This is probably a common query/problem, but my SQL is rusty after doing front-end development for so long. Any help is greatly appreciated!</p>

<p>Oh, btw, I'm using SQL 2005 for this query so if there are any helpful new features that might help me let me know.</p>

[{'answer_id': 17206, 'author': 'Christian Hagelid', 'author_id': 202, 'author_profile': 'https://Stackoverflow.com/users/202', 'pm_score': 1, 'selected': False, 'text': '<p>I could be wrong but shouldn\'t you be using a full outer join instead of just a left join? That way you will be getting \'empty\' columns from both tables.</p>\n\n<p><a href="http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join" rel="nofollow noreferrer">http://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join</a></p>\n'}, {'answer_id': 17265, 'author': 'Kevin Crumley', 'author_id': 1818, 'author_profile': 'https://Stackoverflow.com/users/1818', 'pm_score': 2, 'selected': False, 'text': '<p>@Christian -- markdown editor -- UGH; especially when the preview and the final version of your post disagree...\n@Christian -- full outer join -- the full outer join is overruled by the fact that there are references to SP1 in the WHERE clause, and the WHERE clause is applied after the JOIN.  To do a full outer join with filtering on one of the tables, you need to put your WHERE clause into a subquery, so the filtering happens <em>before</em> the join, or try to build all of your WHERE criteria onto the JOIN ON clause, which is insanely ugly.  Well, there\'s actually no pretty way to do this one.</p>\n\n<p>@Jonas: Considering this:</p>\n\n<blockquote>\n  <p>Also, the report is actually for financial year - so <strong>I would love to have empty columns with 0 in both if there was no sales in say month 5 for either 2007 or 2008.</strong></p>\n</blockquote>\n\n<p>and the fact that this job can\'t be done with a pretty query, I would definitely try to get the results you actually want.  No point in having an ugly query and not even getting the exact data you actually want. ;)</p>\n\n<p>So, I\'d suggest doing this in 5 steps:<br>\n1. create a temp table in the format you want your results to match<br>\n2. populate it with twelve rows, with 1-12 in the month column<br>\n3. update the "This Year" column using your SP1 logic<br>\n4. update the "Last Year" column using your SP2 logic<br>\n5. select from the temp table</p>\n\n<p>Of course, I guess I\'m working from the assumption that you can create a stored procedure to accomplish this.  You might technically be able to run this whole batch inline, but that kind of ugliness is very rarely seen.  If you can\'t make an SP, I suggest you fall back on the full outer join via subquery, but it won\'t get you a row when a month had no sales either year.</p>\n'}, {'answer_id': 17277, 'author': 'Jonas Follesø', 'author_id': 1199387, 'author_profile': 'https://Stackoverflow.com/users/1199387', 'pm_score': 1, 'selected': False, 'text': "<p>About the markdown - Yeah that is  frustrating. The editor did preview my HTML table, but after posting it was gone -  So had to remove all HTML formatting from the post...</p>\n\n<p>@kcrumley I think we've reached similar conclusions. This query easily gets real ugly. I actually solved this before reading your answer, using a similar (but yet different approach). I have access to create stored procedures and functions on the reporting database. I created a Table Valued function accepting a product category and a financial year as the parameter. Based on that the function will populate a table containing 12 rows. The rows will be populated with data from the view if any sales available, if not the row will have 0 values.</p>\n\n<p>I then join the two tables returned by the functions. Since I know all tables will have twelve roves it's allot easier, and I can join on Product Category and Month:</p>\n\n<pre><code>SELECT \n    SP1.Program,\n    SP1.Year,\n    SP1.Month,\n    SP1.TotalRevenue AS ThisYearRevenue,\n    SP2.TotalRevenue AS LastYearRevenue\nFROM GetFinancialYear(@Category, 'First Look',  2008) AS SP1 \n     RIGHT JOIN GetFinancialYear(@Category, 'First Look',  2007) AS SP2 ON \n         SP1.Program = SP2.Program AND \n         SP1.Month = SP2.Month\n</code></pre>\n\n<p>I think your approach is probably a little cleaner as the GetFinancialYear function is quite messy! But at least it works - which makes me happy for now ;)</p>\n"}, {'answer_id': 17290, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 4, 'selected': True, 'text': '<p>The Case Statement is my best sql friend.  You also need a table for time to generate your 0 rev in both months.</p>\n\n<p>Assumptions are based on the availability of following tables:</p>\n\n<blockquote>\n  <p>sales: Category | Revenue  |  Yearh  |\n  Month</p>\n</blockquote>\n\n<p>and</p>\n\n<blockquote>\n  <p>tm: Year | Month  (populated with all\n  dates required for reporting)</p>\n</blockquote>\n\n<p>Example 1 without empty rows:</p>\n\n<pre><code>select\n    Category\n    ,month\n    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year\n    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year\n\nfrom\n    sales\n\nwhere\n    year in (2008,2007)\n\ngroup by\n    Category\n    ,month\n</code></pre>\n\n<p>RETURNS:</p>\n\n<pre><code>Category  |  Month  |  Rev. This Year  |  Rev. Last Year\nBikes          1          10 000               0\nBikes          2          12 000               11 000\nBikes          3          12 000               11 500\nBikes          4          0                    15 400\n</code></pre>\n\n<p>Example 2 with empty rows:\nI am going to use a sub query (but others may not) and will return an empty row for every product and year month combo.</p>\n\n<pre><code>select\n    fill.Category\n    ,fill.month\n    ,SUM(CASE WHEN YEAR = 2008 THEN Revenue ELSE 0 END) this_year\n    ,SUM(CASE WHEN YEAR = 2007 THEN Revenue ELSE 0 END) last_year\n\nfrom\n    sales\n    Right join (select distinct  --try out left, right and cross joins to test results.\n                   product\n                   ,year\n                   ,month\n               from\n                  sales --this ideally would be from a products table\n                  cross join tm\n               where\n                    year in (2008,2007)) fill\n\n\nwhere\n    fill.year in (2008,2007)\n\ngroup by\n    fill.Category\n    ,fill.month\n</code></pre>\n\n<p>RETURNS:</p>\n\n<pre><code>Category  |  Month  |  Rev. This Year  |  Rev. Last Year\nBikes          1          10 000               0\nBikes          2          12 000               11 000\nBikes          3          12 000               11 500\nBikes          4          0                    15 400\nBikes          5          0                    0\nBikes          6          0                    0\nBikes          7          0                    0\nBikes          8          0                    0\n</code></pre>\n\n<p>Note that most reporting tools will do this crosstab or matrix functionality, and now that i think of it SQL Server 2005 has pivot syntax that will do this as well.</p>\n\n<p>Here are some additional resources.\nCASE\n<a href="https://web.archive.org/web/20210728081626/https://www.4guysfromrolla.com/webtech/102704-1.shtml" rel="noreferrer">https://web.archive.org/web/20210728081626/https://www.4guysfromrolla.com/webtech/102704-1.shtml</a>\nSQL SERVER 2005 PIVOT\n<a href="http://msdn.microsoft.com/en-us/library/ms177410.aspx" rel="noreferrer">http://msdn.microsoft.com/en-us/library/ms177410.aspx</a></p>\n'}, {'answer_id': 25600, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 2, 'selected': False, 'text': '<p>The trick is to do a FULL JOIN, with ISNULL\'s to get the joined columns from either table. I usually wrap this into a view or derived table, otherwise you need to use ISNULL in the WHERE clause as well.</p>\n\n<pre><code>SELECT \n    Program,\n    Month,\n    ThisYearTotalRevenue,\n    PriorYearTotalRevenue\nFROM (\n    SELECT \n        ISNULL(ThisYear.Program, PriorYear.Program) as Program,\n        ISNULL(ThisYear.Month, PriorYear.Month),\n        ISNULL(ThisYear.TotalRevenue, 0) as ThisYearTotalRevenue,\n        ISNULL(PriorYear.TotalRevenue, 0) as PriorYearTotalRevenue\n    FROM (\n        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue \n        FROM PVMonthlyStatusReport \n        WHERE Year = @FinancialYear \n        GROUP BY Program, Month\n    ) as ThisYear \n    FULL OUTER JOIN (\n        SELECT Program, Month, SUM(TotalRevenue) as TotalRevenue \n        FROM PVMonthlyStatusReport \n        WHERE Year = (@FinancialYear - 1) \n        GROUP BY Program, Month\n    ) as PriorYear ON\n        ThisYear.Program = PriorYear.Program\n        AND ThisYear.Month = PriorYear.Month\n) as Revenue\nWHERE \n    Program = \'Bikes\'\nORDER BY \n    Month\n</code></pre>\n\n<p>That should get you your minimum requirements - rows with sales in either 2007 or 2008, or both. To get rows with no sales in either year, you just need to INNER JOIN to a 1-12 numbers table (you do <a href="http://sqljunkies.com/WebLog/amachanic/articles/NumbersTable.aspx" rel="nofollow noreferrer">have one of those</a>, don\'t you?).</p>\n'}, {'answer_id': 44827376, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 0, 'selected': False, 'text': "<p>Using pivot and Dynamic Sql we can achieve this result</p>\n\n<pre><code>SET NOCOUNT ON\nIF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL\nDROP TABLE #TEMP\n\n;With cte(Category , Revenue  ,  Yearh  ,  [Month])\nAS\n(\nSELECT 'Bikes', 10000, 2008,1 UNION ALL\nSELECT 'Bikes', 12000, 2008,2 UNION ALL\nSELECT 'Bikes', 12000, 2008,3 UNION ALL\nSELECT 'Bikes', 15000, 2008,1 UNION ALL\nSELECT 'Bikes', 11000, 2007,2 UNION ALL\nSELECT 'Bikes', 11500, 2007,3 UNION ALL\nSELECT 'Bikes', 15400, 2007,4\n)\nSELECT * INTO #Temp FROM cte\n\nDeclare @Column nvarchar(max),\n        @Column2 nvarchar(max),\n        @Sql nvarchar(max)\n\n\nSELECT @Column=STUFF((SELECT DISTINCT ','+ 'ISNULL('+QUOTENAME(CAST(Yearh AS VArchar(10)))+','+'''0'''+')'+ 'AS '+ QUOTENAME(CAST(Yearh AS VArchar(10)))\nFROM #Temp order by 1 desc FOR XML PATH ('')),1,1,'')\n\nSELECT @Column2=STUFF((SELECT DISTINCT ','+ QUOTENAME(CAST(Yearh AS VArchar(10)))\nFROM #Temp FOR XML PATH ('')),1,1,'')\n\nSET @Sql= N'SELECT Category,[Month],'+ @Column +'FRom #Temp\n            PIVOT\n            (MIN(Revenue) FOR yearh IN ('+@Column2+')\n            ) AS Pvt\n\n            '\nEXEC(@Sql)\nPrint @Sql\n</code></pre>\n\n<p>Result</p>\n\n<pre><code>Category    Month   2008    2007\n----------------------------------\nBikes       1       10000   0\nBikes       2       12000   11000\nBikes       3       12000   11500\nBikes       4       0       15400\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/17194', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1199387/']