sub query not evaluating parameter correctly

Question

Needed to start a new thread to deal with a new issue from old problem.

Original problem is at this link.... complex query join checking that value does not exist

I have two tables, first has

Tb1 = drID, schedDate, rteID

Second has:

Tb2  = drID, FName, LName, Active

tb1 schedDate has a value for 11/12/2012 but returns no records, if I enter 11/01/2012 I get the correct records back, but this date does not exist on any record in tb1.

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
AND   drID NOT IN (
      SELECT drID
      FROM   Tb1
      WHERE  (drID IS NULL OR drID = '')
      AND  (schedDate = (@targetDate)
      )

Both date fields are dates I have tried casting each individually and both together as in...

AND (CAST(schedDate AS DATE) = CAST(@targetDate AS DATE))

and

AND (CAST(schedDate AS DATETIME) = CAST(@targetDate AS DATETIME))

I am trying to check that no drID exists in tb1 on the date submitted.

Jake1164 · Answer 1 · 2012-12-28T12:17:43.073

2

I think you have a fundamental flaw in your subquery:

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
AND   drID NOT IN (
  -- You are looking for drID's not included in the following subquery.
  SELECT drID
  FROM   Tb1
  WHERE  (drID IS NULL OR drID = '')
  AND  (schedDate = (@targetDate)
  )

Your subquery only returns null or '' drID's.

  SELECT drID
  FROM   Tb1
  WHERE  (drID IS NULL OR drID = '')
  AND  (schedDate = (@targetDate)

edit:

To be more clear, the outer query returns all drID, Fname, LName from table2 where the active flag is set to true and the drID IS NOT INCLUDED IN the list of drID's returned by the sub query. Your sub query never returns a drID.

Remove the (drID IS NULL or dirID = '') from the sub query.

edited Dec 28 '12 at 12:17

answered Dec 21 '12 at 19:59

Jake1164

12,291
6
47
64

From all of this I need to return from TB2 drID, Fname, LName Where Active = True and drID does not exist on any record in tb1 for the schedDate selected. There are many tb1 rteID records for any possible date. – htm11h Dec 27 '12 at 20:01
The NOT IN portion of the main query takes care of filtering out any drID's that exist in the second query. – Jake1164 Dec 28 '12 at 12:10
I made those changes and it still does not work unless there is at least one record to be excluded by matching a drID from TB2. If Tb1 has no records on the submitted date there is nothing returned at all, when this should actually return all drIDs from TB2. – htm11h Jan 03 '13 at 17:17

score 0 · Answer 2 · answered Dec 21 '12 at 19:52

This will remove any hour, mins secs and smaller time information from the two datetime variables before the compare.

SELECT drID, Fname, LName
FROM   TB2
WHERE  Active = True 
AND   drID NOT IN (
      SELECT drID
      FROM   Tb1
      WHERE  (drID IS NULL OR drID = '')
      AND  (dateadd(day,datediff(day,0,schedDate),0) = 
            dateadd(day,datediff(day,0,@targetDate),0)
           )

Beth · Answer 3 · 2012-12-21T20:12:30.503

0

Does this work?

SELECT drID, Fname, LName
FROM   TB2 left outer join tb1 on
  tb2.drid = tb1.drid and
  tb1.active = true and
  schedDate = @targetDate
WHERE  
  tb1.drid is null

if it's the date test for equality that's causing it to fail, you could also try

where abs(datediff(day,@targetDate,schedDate)) < 1

edited Dec 21 '12 at 20:12

answered Dec 21 '12 at 19:54

Beth

9,531
1
24
43

What if datediff returns -2? (Then your solution fails) – Hogan Dec 21 '12 at 19:57
added `abs()` I really don't know if that's the problem, tho – Beth Dec 21 '12 at 20:14
From all of this I need to return from TB2 drID, Fname, LName Where Active = True and drID does not exist on any record in tb1 for the schedDate selected. There are many tb1 rteID records for any possible date. – htm11h Dec 27 '12 at 20:00

score 0 · Accepted Answer · answered Jan 03 '13 at 19:08

0

This was the correct solution for this question....

WHERE not exists (SELECT drID FROM tb1 ....

Date values where not the issue.

Thanks for all of the feed back.

answered Jan 03 '13 at 19:08

htm11h

1,739
8
47
104

sub query not evaluating parameter correctly

4 Answers4