Missing record in SQL Query

I have the following sql query and the results are missing one day 11/30/14.  I need to have a day show up even if the sales table doesn't include a record for that day, which is the case for this date.  I have verified that the Calendar table does have a 11/30/14 record and I thought I had the joins correct so that the record will show, but to no avail.   Any suggestions?  I have a feeling the problem is associated with the location table, which is how the location name is standardized.  

SELECT     CASE WHEN Locations.[Location Name] IS NULL THEN @Location ELSE Locations.[Location Name] END AS Location, Calendar.Dt, 
	                      CASE WHEN Sales.[Net Sales] IS NULL THEN 0 ELSE Sales.[Net Sales] END AS CurrentSales, LEFT(DATENAME(dw, Calendar.Dt), 3) AS DayName, DATEPART(isowk, 
	                      Calendar.Dt) AS WeekNum
	FROM         Locations LEFT OUTER JOIN
	                      Sales ON Locations.LocationNameOrig = Sales.[Location Name] RIGHT OUTER JOIN
	                      Calendar ON Sales.Date = Calendar.Dt
	WHERE     (Locations.[Location Name] = @Location) AND (Calendar.Dt BETWEEN @Begin AND @End) OR
	                      (Locations.[Location Name] IS NULL) AND (Calendar.Dt BETWEEN @Begin AND @End)
	ORDER BY Calendar.Dt

Open in new window

rcowen00Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
If you wish every date in the Calendar table to appear in the return set, then you'll need the Calendar table on the LEFT side of the JOIN..

FROM Calendar
   LEFT JOIN Sales ON ...
   JOIN Locations ON ...

Open in new window

btw if you want to compare notes on Calendar tables check out my article on Building your own SQL calendar table to perform complex date expressions
0
 
rcowen00Author Commented:
aaaahhhh so stupid.  Thank you!
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Anytime.  Far better to ask us stupid questions then ask your co-workers and have them wonder why you're asking stupid questions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.