DangerZone DangerZone - 3 years ago 67
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

for records that have been created before a specified date (
column) unless this record is referenced by another (by the
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 Source

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

FROM MyDB.dbo.[myTable] t1
                  WHERE t1.id = ref_id 
                  AND created > @myDate) 
AND created < @myDate

Sample Demo

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download