Matrix to show all months of the year

Question

I have a Web Service that I am getting data from (Not mine and I can't change it)

The data is in the following format

<DealMetrics>
  <DealId>1</DealId> 
  <DealName>ABC</DealName> 
  <FundAbbreviation>ABC</FundAbbreviation> 
  <NavYear>2012</NavYear> 
  <NavMonth>January</NavMonth> 
  <Nav>123</Nav>
.
.
.
</DealMetrics>

I have a matrix that displays this performance data and works fine - I have a row group on the year and a column group on the month. The problem occurs if a deal has only been running for a short time - I want to display all months regardless of whether we have any data - So for example if a deal started in September and we're in December I'd want the column headings to display for Jan - Aug as well as the data that is returned in the web service.

Any ideas?

Regards Andy

score 0 · Answer 1 · answered Jan 13 '14 at 11:40

Might not be the most elegant way of doing it (I'm a relative newbie to SQL!) but I would create a table which is effectively a calendar of all the months within the period you are interested in (using a recursive CTE) and then just join the results of the query from the web service onto that?

score 0 · Answer 2 · edited May 23 '17 at 10:25

Assuming your metrics end up in a SQL Server table with the following columns:

-- Metrics table:
DealId
DealName
FullAbbreviation
NavYear
NavMonth
Nav

Create a calendar table to hold all years and months you could possibly be interested in. For instructions on how to create a calendar table, check out this post, for example: How to create a Calender table for 100 years in Sql

Then, when you have your calendar table, use it in an outer join with your metrics table, so that you get all your years and months from the calendar table, and the metric data (if any). The query in your dataset could for example look like this:

SELECT DealId,
    DealName,
    FullAbbreviation,
    ISNULL(NavYear, Months.Year) AS NavYear,
    ISNULL(NavMonth, Months.Month) AS NavMonth,
    Nav
FROM
    (SELECT DISTINCT Year, Month, MonthNumber FROM Calendar) AS Months
LEFT JOIN Metrics ON Months.Year = Metrics.NavYear AND Months.Month = Metrics.NavMonth
ORDER BY NavYear, NavMonth

Matrix to show all months of the year

2 Answers2