SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Non re-occuring records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

32 Posts

Posted - 04/02/2013 :  16:04:09  Show Profile  Reply with Quote
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.

Edited by - WebKill on 04/02/2013 17:21:10

Flowing Fount of Yak Knowledge

6062 Posts

Posted - 04/02/2013 :  16:35:42  Show Profile  Reply with Quote
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

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

Be One with the Optimizer

Edited by - TG on 04/02/2013 16:37:29
Go to Top of Page

Starting Member

32 Posts

Posted - 04/02/2013 :  17:23:06  Show Profile  Reply with Quote
That would still require looping through a cursor list of clientid, I was hoping to get out of that.

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.

Edited by - WebKill on 04/02/2013 17:34:33
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000