DangerZone DangerZone - 1 month ago 4
SQL Question

Getting IDs where created before a specific date and not referenced after that date

I'm trying to get a list of IDs back from a query that depends on the value in an ID reference column and date column in another row.

Basically, I'm trying to retrieve the

ID
for records that have been created before a specified date (
Created
column) unless this record is referenced by another (by the
RefID
column) created after this date.

(I plan to use this in a sproc at some point, but just using a variable for the time being)

Very simple example table below. Left out unrelated columns for simplicity.

+--------+---------+---------------+
| ID | RefID | Created |
+--------+---------+---------------+
| 123 | NULL | 2016-10-18 |
| 456 | 123 | 2016-10-20 |
| 789 | NULL | 2016-10-18 |
| 111 | 789 | 2016-10-18 |
+--------+---------+---------------+


So in my example table above, if I specified 2016-10-19 I would get IDs 789 and 111 back. I would not get 123 because although it was created before 2016-10-18, it is referenced by 456 which was not (therefore I would also not get 456 back).

I've been fiddling with variations on joining, but SQL is definitely not my strong area, so I feel like I'm running in circles, and chasing the wrong solution at that!

I have this so far, which I think is getting me the correct list of IDs referenced after a certain date. I'm not quite sure how to exclude this list from my main query...let alone what an appropriate main query would be.

DECLARE @myDate datetime
SET @myDate = '2016-10-19'

SELECT t1.id
FROM MyDB.dbo.[myTable] AS t1
JOIN MyDB.dbo.[myTable] AS t2
ON t1.id = t2.ref_id
WHERE t1.id = t2.ref_id AND t2.created > @myDate

vkp vkp
Answer

You can get it with not exists with a condition to check if the referenced id has a row after the specified date.

SELECT id
FROM MyDB.dbo.[myTable] t1
WHERE NOT EXISTS (SELECT 1 FROM MyDB.dbo.[myTable] 
                  WHERE t1.id = ref_id 
                  AND created > @myDate) 
AND created < @myDate

Sample Demo