Easy!! Subquery needs to show all Sales Reps, even when null

I have a subquery that grabs all the sales reps with less then 6 visits. Only problem is, when i have a date when there are any number of sales reps that dont make visits, so the column would be null, they dont show up. I want to display these, because this report is supposed to show the visits made, so if they made none, i want it to show zero, instead of not showing the whole date column, since zero visits were made on that date that were below 6 and more then zero. Here's my stored procedure: (the subquery is highlighted)

Code Snippet

ALTER PROCEDURE [dbo].[Testing_Visits_6]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Status,

dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],

B.VisitsTotal

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

INNER JOIN ( Select COUNT(Visits)as VisitsTotal,[Sales Responsible],CONVERT(VARCHAR,(Qry_Visits.time_log),110)TheDate,Qry_Visits.Status

FROM dbo.Qry_Visits

WHERE Qry_Visits.Status=2

GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status

HAVING SUM(Visits) < 6)B

ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND

CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2

ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

You'll need to do an outer join to your date dimension

|||

is there a way i can just say, if SUM(visits) is null then 0

or something like that?

|||

either this...

coalesce(sum(visits),0)

or this...

case

when sum(visits) is null then 0

else sum(visits)

end as 'SumVisits'

|||I guess technically this doesnt work because it doesnt change anything. I guess i just need to find a way to Display all dates that have visits, since all dates show up on my other report that displays all visits, no matter how many. Would i do another subquery to grab all dates where Visit is greater then zero?|||

Replace "INNER JOIN" with "LEFT OUTER JOIN", try the following...

ALTER PROCEDURE [dbo].[Testing_Visits_6]

(@.Region_Key int=null)

AS

BEGIN

SELECT dbo.Qry_Visits.Status,

dbo.Qry_Visits.Customer_code,

Qry_Sales_Group.Name,

dbo.Qry_Sales_Group.SR_Name,

dbo.Qry_Date_Dim.Date_Dimension_Fiscal_Week,

dbo.Qry_Date_Dim.Date_Dimension_Date,

dbo.Qry_Date_Dim.Day_Of_Month,

dbo.Qry_Sales_Group.Region,

dbo.Qry_Visits.period_code,

dbo.Qry_Visits.cycle_day, dbo.Qry_Visits.Visits,

dbo.Qry_Visits.time_log, dbo.Qry_Visits.Mailing_Name,

dbo.Qry_Date_Dim.Date_Dimension_Year,

dbo.Qry_Date_Dim.Date_Dimension_Period,

CONVERT(varchar, dbo.Qry_Visits.time_log, 110) AS Date,

dbo.Qry_Sales_Group.Region_Key, dbo.Qry_Visits.[SR Code],

IsNull(B.VisitsTotal,0) VisitsTotal

FROM dbo.Qry_Visits

INNER JOIN dbo.Qry_Sales_Group

ON dbo.Qry_Visits.[SR Code]

COLLATE SQL_Latin1_General_CP1_CI_AS = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

AND dbo.Qry_Visits.[SR Code] = dbo.Qry_Sales_Group.SalesPerson_Purchaser_Code

COLLATE Latin1_General_CI_AS

INNER JOIN dbo.Qry_Date_Dim

ON CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = CONVERT(varchar, dbo.Qry_Visits.time_log, 110)

LEFT OUTER JOIN (

Select COUNT(Visits)as VisitsTotal

, [Sales Responsible]

, CONVERT( VARCHAR,(Qry_Visits.time_log),110) as TheDate

, Qry_Visits.Status

FROM dbo.Qry_Visits

WHERE Qry_Visits.Status=2

GROUP by [Sales Responsible] , CONVERT(VARCHAR,(Qry_Visits.time_log),110),Qry_Visits.Status

HAVING SUM(Visits) < 6

)B

ON dbo.Qry_Sales_Group.SR_Name COLLATE Latin1_General_CI_AS = B.[Sales Responsible] COLLATE Latin1_General_CI_AS AND

CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110) = B.TheDate

WHERE REGION_KEY=@.Region_Key and Qry_Visits.Status=2

ORDER BY dbo.Qry_Sales_Group.SR_Name, CONVERT(varchar, dbo.Qry_Date_Dim.Date_Dimension_Date, 110)

END

sql