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

Report
Question

Please briefly explain why you feel this question should be reported .

Report Cancel

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?

solved 0
1 Answer 15 views 0

Answer ( 1 )

    0
    January 12, 2017 at 9:00 am

    Please briefly explain why you feel this answer should be reported .

    Report Cancel

    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).

    Best answer

Leave an answer

Browse

What is the capital of Egypt ? ( Cairo )

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>