Bit of a complicated one for me.
I have a database full of hundreds of thousands of records, many of which are duplicated.
I need to get all records within the last year but making sure every instance of that record is within the last year, e.g. if a record is duplicated and one is older than a year this shouldn't be included.
So far I have the below...
Step 1 - find out earliest date for each record
SELECT MIN(CreateDate) AS Date, Email FROM Results R WHERE (R.Email IS NOT NULL AND R.Email <> '') GROUP BY R.Email
I created this as a view and called it
Step 2 - grab all within the last year
Note: I need records within the last year but they also need to be in a log table also. So all records within the last year that are also present in some log tables.
So far I have done this...
SELECT * FROM EarliestInteraction ECI WHERE ( CAST(ECI.Date AS DATE) >= CAST(GETDATE() - 365 AS DATE) ) AND ( EXISTS ( SELECT Id FROM LOG1 R WHERE Source = 'LOGGED' AND R.Email = ECI.Email ) OR EXISTS ( SELECT Id FROM LOG2 R WHERE (R.Email IS NOT NULL AND R.Email <> '') AND R.Email = ECI.Email AND R.EventType IN ( 'LOGGED' )) )
My question is, is this a good way of doing this and accurate?
Or am I missing something that would bring back earlier duplicates...
Any thoughts on if this is accurate or achieves the brief would be great.