Solved

Missing record in SQL Query

Posted on 2014-12-29
3
249 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 65

Accepted Solution

by:
Jim Horn earned 500 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 65

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore 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.
In this article I will describe the Copy Database Wizard 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.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now