Help New Query Needed

Question

Help New Query Needed

j.hoube

SSC Enthusiast

Points: 112
More actions
March 14, 2015 at 1:44 am

#295953

Hi,
I'm stuck with a database query.
I have two tables one lists sites names while the other lists the completed tasks with the date of completion for the sites (the same site could have completed the same task but on a different date).
What I need to do is create a query which shows me a list of sites which don't have a date in the second table, now here is the bit I really struggle with...... it needs to be between a certain date.
Every time I add a WHERE clause in to my query to define the dates it removes the sites which don't have a task date.
I know its possible any ideas on the best way to tackle it?
Thanks in advance
J

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63510 More actions · Answer 1

I have two tables one lists sites names while the other lists the completed tasks with the date of completion for the sites (the same site could have completed the same task but on a different date).
What I need to do is create a query which shows me a list of sites which don't have a date in the second table, now here is the bit I really struggle with...... it needs to be between a certain date

.

One way to do it is with a correlated subquery. Without actual table definitions, this is as close as I could get. Note the aliasing of the Site

table, because it's what allows the correlation to work in the subquery.

CREATE PROC GetSitesWithoutCompleteDates

@StartDate DATE, @EndDate DATE

AS

SELECT S.SiteName

FROM Site S

WHERE NOT EXISTS (SELECT 1 FROM SiteTask st WHERE st.SiteID=S.SiteID AND TaskDate BETWEEN @StartDate AND @EndDate);

roger.price-1150775 Default port Points: 1458 More actions · Answer 2

Select * from site

Left outer join sitedate on site.siteid = sitedate.siteid and sitedate.date between @startedate And @enddate

Where sitedate.date IS NULL

Left outer join putting the date filter not the join.

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

It doesn't matter much because of the high speed machines available but the WHERE NOT EXISTS will be a bit more efficient than the LEFT JOIN with the NULL detection.

I'd also get out of the habit of ever using BETWEEN for anything temporal even if you're supposedly "guaranteed" to be working only with "whole" dates. I've seen the requirements for such things change many times over the years and finding all of the code affected, fixing it, and then doing full regression testing on it is pretty expensive especially since you usually don't find all of the affected code on the first go around.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)