Help with SQL LEFT OUTER JOIN

[Former Member]

I am using BOBJ Edge 3.1 SP2. I am creating a derived table with the following code:

SELECT DISTINCT 
                      TOP (100) PERCENT sv.ReportNum, sv.Stmt, sv.Response_Number, sv.Characteristic_Text, sv.SurveyNum, sv.BStmt, 
                      SUM(CASE WHEN d .Score IS NOT NULL THEN 1 ELSE 0 END) AS Answer_Count
FROM         vwSurvey AS sv LEFT OUTER JOIN
                      tblCSSDataE AS d ON sv.StmtNum = d.StmtNum AND sv.DStmtNum = d.DStmtNum AND sv.ClientNum = d.CorpNum AND 
                      sv.Score_Score = d.Score
GROUP BY sv.ReportNum, sv.Stmt, sv.Response_Number, sv.Characteristic_Text, sv.SurveyNum, sv.BStmt
ORDER BY sv.ReportNum, sv.Response_Number

There is a SurveyDate field in tblCSSDataE. I have created a Year and Quarter dimensions against this field. My problem is when I use a date filter the query is generated with a WHERE clause against the SurveyDate field which is as expected. What this does in effect though is change my LEFT OUTER JOIN back to an INNER JOIN. What I lose from this is instead of every sv.Response_Number and sv.Characteristic_Text showing up even though they have 0 as an Answer_Count I only get the Response_Number and Characteristic_Text that have counts.

If I change the WHERE to an AND then I get the correct results. Showing every Response_Number and Characteristic_Text, even the 0's which is what I want. Here is how I want the query to look like after I filter the data.

SELECT DISTINCT 
                      TOP (100) PERCENT sv.ReportNum, sv.Stmt, sv.Response_Number, sv.Characteristic_Text, sv.SurveyNum, sv.BStmt, 
                      SUM(CASE WHEN d .Score IS NOT NULL THEN 1 ELSE 0 END) AS Answer_Count
FROM         dbo.vwSurvey AS sv LEFT OUTER JOIN
                      dbo.tblCSSDataE AS d ON sv.StmtNum = d.StmtNum AND sv.DStmtNum = d.DStmtNum AND sv.ClientNum =      d.CorpNum AND sv.Score_Score = d.Score AND d.SurveyDate BETWEEN '10/1/2008' AND '12/31/2008'
GROUP BY sv.ReportNum, sv.Stmt, sv.Response_Number, sv.Characteristic_Text, sv.SurveyNum, sv.BStmt
ORDER BY sv.ReportNum, sv.Response_Number

Is there a way to do this?

Thank you in advance.

[Former Member]

This has been resolved!!!