[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 287
  • Last Modified:

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

0
rcowen00
Asked:
rcowen00
  • 2
1 Solution
 
Jim HornMicrosoft 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

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now