CODEWITHSUNDEEP
sqlsql-serverreporting-services
Bodokh
Bodokh

Reputation: 1066

Add missing Months to a result set of a query

First i want to apologize for the title, it is misleading, I really have no way to explain the question but show what is the problem. I have a table that contains 3 columns: id - id of person dateInvested InvestedAmount Now i have this query which retrieves for each month in a year how much money was invested for a specific Id:

SELECT DATEPART(month, dbo.tblProjectBridge.dateInvested) AS Month
     , SUM(dbo.tblProjectBridge.investmentAmount) AS Amount
FROM dbo.tblProjectBridge 
RIGHT OUTER JOIN dbo.tblInvestor    ON dbo.tblProjectBridge.personId = dbo.tblInvestor.id 
RIGHT OUTER JOIN dbo.tblAgent       ON dbo.tblInvestor.refId = dbo.tblAgent.id 
RIGHT OUTER JOIN dbo.tblSeniorAgent ON dbo.tblAgent.seniorId = dbo.tblSeniorAgent.Id
WHERE (dbo.tblSeniorAgent.Id = @id) 
  AND (DATEPART(year, dbo.tblProjectBridge.dateInvested) = @year)
GROUP BY DATEPART(month, dbo.tblProjectBridge.dateInvested)

Now the query works fine, the problem is I need to assign this query to a chart in reporting services and sometimes there are no investments in a certain month so the month does not appear. For example for one id it might return:

month | amount
1     | 12000
5     | 25241
11    | 52124

I want even months that have no investments in them to appear so it would like this:

month | amount
1     | 12000
2     | 0
3     | 0
4     | 0
5     | 25241
6     | 0

and so on... How do i get around doing this?

Upvotes: 1

Views: 4503

Answers (1)

M.Ali
M.Ali

Reputation: 69524

Query

;WITH MissingMonths
 AS 
   (
    SELECT number
    FROM master..spt_values
    WHERE number > 0 AND number < 13 
    GROUP BY number
    ),
Your_Query
AS
  (
   -- Inside this CTE you will put you existing query
  SELECT 1 AS [Month], 12000 AS [Amount] 
  UNION ALL SELECT 5, 25241
  UNION ALL SELECT 11,52124
  )
SELECT COALESCE(number,[month]) AS [Month]
      ,COALESCE(amount, 0)      AS Amount
FROM   MissingMonths MM 
LEFT JOIN Your_Query Q   ON MM.number = Q.[month]

Result

╔═══════╦════════╗
║ Month ║ Amount ║
╠═══════╬════════╣
║     1 ║  12000 ║
║     2 ║      0 ║
║     3 ║      0 ║
║     4 ║      0 ║
║     5 ║  25241 ║
║     6 ║      0 ║
║     7 ║      0 ║
║     8 ║      0 ║
║     9 ║      0 ║
║    10 ║      0 ║
║    11 ║  52124 ║
║    12 ║      0 ║
╚═══════╩════════╝

Upvotes: 3

Related Questions