Non re-occuring records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Non re-occuring records

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2013-04-02 : 16:04:09

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.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-04-02 : 16:35:42

If you want to find the rows that do not have run dates in the last week how about something like

select <same as group by>
from Data
where rundate > getdate()-12
and nr = 1
and clientid = @clientid
group by <whatever data1, data2?>
having max(rundate) < getdate()-6

EDIT:
for all clients at once just add clientid to select and group by

Be One with the Optimizer
TG

WebKill
Starting Member

32 Posts

Posted - 2013-04-02 : 17:23:06

That would still require looping through a cursor list of clientid, I was hoping to get out of that.

EDIT:
Doing them all at once, the max(rundate) would return max for all clients, so if client b did not have data uploaded but client a did, it would act as if it did for client b, and would take incorrect action.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources