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 [EarliestInteraction]
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.