Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Missing record in SQL Query

Posted on 2014-12-29
3
Medium Priority
?
283 Views
Last Modified: 2014-12-29
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
Comment
Question by:rcowen00
  • 2
3 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40522369
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
 

Author Comment

by:rcowen00
ID: 40522385
aaaahhhh so stupid.  Thank you!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40522476
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question