Sql Server Query - won't display zero returns

[JCCDEVEL]

Hello,

I'm writing a basic query in Sql Server Mgmt Studio 2005. Basically, I'm trying to query a table to get all "issues" reported in a month by "Project Category". The query is working fine except that I cannot get it to display entries for projects that have zero issues for that month - so it comes out blank. I'm pretty sure I need a left join (or something like that) for this, but no matter what I try, those zero entries won't display. Here is the query

     DECLARE @StartDate datetime, @EndDate datetime,  @ProjectID     int
        DECLARE c1 CURSOR FOR
        SELECT Pj_ID
        FROM dbo.projects
 
        OPEN c1
 
        FETCH NEXT FROM c1
        INTO @ProjectID
 
        WHILE @@FETCH_STATUS = 0
        BEGIN
 
        Select  bg.bg_project as PROJECT, pr.pj_name as CATEGORY,  count (bg.bg_project) as TOTAL  
        from projects pr left outer join  bugs bg 
        on pr.pj_id =bg.bg_project 
        where bg.bg_project = @ProjectID and
        ((bg_reported_date > '08/01/2007') AND (bg_reported_date < '09/01/07')) 
        group by bg.bg_project, pr.pj_name
        FETCH NEXT FROM c1
        INTO @ProjectID
 
        END
        CLOSE c1
        DEALLOCATE c1
 

The results look something like this when imported into Excel:

PROJECT CATEGORY TOTAL
----------- --------------------------------------------------------------------------------
22 EDI 9

PROJECT CATEGORY TOTAL
----------- --------------------------------------------------------------------------------

Thank you in advance for your time!

JCC

[debasisdas]

Please post all your involved tables structures.

[azimmer]

Hello,

I'm writing a basic query in Sql Server Mgmt Studio 2005. Basically, I'm trying to query a table to get all "issues" reported in a month by "Project Category". The query is working fine except that I cannot get it to display entries for projects that have zero issues for that month - so it comes out blank. I'm pretty sure I need a left join (or something like that) for this, but no matter what I try, those zero entries won't display. Here is the query

     DECLARE @StartDate datetime, @EndDate datetime,  @ProjectID     int
        DECLARE c1 CURSOR FOR
        SELECT Pj_ID
        FROM dbo.projects
 
        OPEN c1
 
        FETCH NEXT FROM c1
        INTO @ProjectID
 
        WHILE @@FETCH_STATUS = 0
        BEGIN
 
        Select  bg.bg_project as PROJECT, pr.pj_name as CATEGORY,  count (bg.bg_project) as TOTAL  
        from projects pr left outer join  bugs bg 
        on pr.pj_id =bg.bg_project 
        where bg.bg_project = @ProjectID and
        ((bg_reported_date > '08/01/2007') AND (bg_reported_date < '09/01/07')) 
        group by bg.bg_project, pr.pj_name
        FETCH NEXT FROM c1
        INTO @ProjectID
 
        END
        CLOSE c1
        DEALLOCATE c1
 

The results look something like this when imported into Excel:

PROJECT CATEGORY TOTAL
----------- --------------------------------------------------------------------------------
22 EDI 9

PROJECT CATEGORY TOTAL
----------- --------------------------------------------------------------------------------

Thank you in advance for your time!

JCC

I guess your problem lies in the WHERE clause:

where bg.bg_project = @ProjectID and
        ((bg_reported_date > '08/01/2007') AND (bg_reported_date < '09/01/07')) 
 

These conditions can only be met if there's corresponding data in the bugs (bg) table; in case of the left outer join these fields can end up having NULL in them. I suggest you change it to

where pr.pj_id = @ProjectID and
        (((bg_reported_date > '08/01/2007') AND (bg_reported_date < '09/01/07')) OR (bg_reported_date is null))
 

or something like this.
Note: you may want to rephrase the selected columns as well along these lines.

[JCCDEVEL]

Thank you so much for your help. With a little tweaking, this did the trick!

I guess your problem lies in the WHERE clause:

where bg.bg_project = @ProjectID and
        ((bg_reported_date > '08/01/2007') AND (bg_reported_date < '09/01/07')) 
 

These conditions can only be met if there's corresponding data in the bugs (bg) table; in case of the left outer join these fields can end up having NULL in them. I suggest you change it to

where pr.pj_id = @ProjectID and
        (((bg_reported_date > '08/01/2007') AND (bg_reported_date < '09/01/07')) OR (bg_reported_date is null))
 

or something like this.
Note: you may want to rephrase the selected columns as well along these lines.