SSRS SQL Query to show the zero value for non existing records

Question

I am using the following query to pull the data for last 11/12 months

SELECT [ID]
      ,[Name]  
      ,[Amount]     
FROM DB1 
WHERE [Period] BETWEEN @FromMonth AND @ToMonth  
      AND [Desc] = 'XYZ'  

What I want to achieve is, some IDs does not have a any record for some periods(month) with no amount. Currently the query doesn't return anything for those combination of names and months where there is no record in DB. It only displays a result if the ID has a certain amount and date in db. However, what I want is that if an ID doesn't have an amount for a month then it should show the amount for that month.

@FromMonth and @ToMonth are the parameters being passed to the report. I know I can achieve this by pivoting but I don't want to do that. Any hints?

Answer 1

If I had to guess based on your title, perhaps this is what you're looking for using a case statement:

select id, name, 
    case when Period between @FromMonth and ToMonth and [Desc] = 'XYZ' 
         then amount 
         else 0 
    end as amount
from db1

This will return all ids and names from db1, but only include the amount for those records matching your previous where criteria.

---

By the way, I'd typically recommend avoid using reserved words as column names (desc).