Hi,
I have this query but for some reason the StartDate values are not getting filtered. What's wrong with it? Please let me know.
SELECT *
FROM TableA A
WHERE NOT EXISTS( SELECT * FROM TableB B
WHERE A.pId = B.pId and A.StartDate >= '20-JUN-10' )
Did you mean this?
SELECT *
FROM TableA A
WHERE A.StartDate >= '20-JUN-10'
AND NOT EXISTS( SELECT * FROM TableB B WHERE A.pId = B.pId )
Putting the condition with StartDate into the NOT EXISTS-clause should return those rows where this criteria is not matched.
At a guess, you may need to revise your parenthesis:
SELECT * FROM TableA A
WHERE NOT EXISTS(SELECT * FROM TableB B WHERE A.pId = B.pId)
and A.StartDate >= '20-JUN-10'
use ISO formats for dates so instead of '20-JUN-10' use '20100610' assuming it is a datetime/date column
If StartDate is a varchar column, then you cannot expect to get correct results when doing greater than comparisons on it. In effect, you are saying that any of the values that would be stored in the StartDate column should not sort after or on '20-JUN-10'. You should make StartDate an actual DateTime. So, until you do that, you should cast it to a DateTime and since it is only referencing the outer table, you can pull it out of the subquery:
Select ..
From TableA As A
Where ( A.StartDate Is Null Or Cast(A.StartDate As DateTime) < '2010-06-20' )
And Not Exists (
Select 1
From TableB As B
Where B.pid = A.pid
)
That StartDate is not an actual DateTime is a fundamental problem of data integrity and creates this problem along with a host of others I would imagine. However, if for some insane reason you have values that cannot be cast to a DateTime in your StartDate column, then you need to add yet another check (and slap the original DBA upside the head):
Select ..
From TableA As A
Where ( A.StartDate Is Null
Or (IsDate(A.StartDate) = 1 And Cast(A.StartDate As DateTime) < '2010-06-20' ) )
And Not Exists (
Select 1
From TableB As B
Where B.pid = A.pid
)