I have a table in which I need to find records from last week (less than date-6) and have not shown back up this week (greater than date-6), these dates are also specific to the Client field, so Client A may have data loaded, but Client B does not, so we wouldn't want to check against Client B yet otherwise it would list all items as not re-occurring from last week.
So far the only way I can find to do this is create a cursor list of clients, then loop through them and check to see if there is data for the week, if there is then I use a query like this to count occurrences and find the older ones (RunDate < GETDATE()-6) that are not counted this week (order by rundate desc, checking for the enumeration = 1)
select * from (SELECT * , row_number() OVER ( PARTITION BY data1, data2 ORDER BY Rundate desc ) AS nr
FROM Data where RunDate > GETDATE()-12)as a where a.nr = 1 and RunDate < GETDATE()-6
and ClientID = @client_id
I know that cursors and loops are not a great way of doing this, combined that this has to run at least several times a day as a part of the data upload process it is getting long and I am running into deadlock issues, I think I need a better solution, I'm just not sure how to get there.