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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

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

WebKill
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

TG
Flowing Fount of Yak Knowledge

USA
6065 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

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

Be One with the Optimizer
TG

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

WebKill
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.

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.

Edited by - WebKill on 04/02/2013 17:34:33
Go to Top of Page
  Previous Topic Topic Next 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.07 seconds. Powered By: Snitz Forums 2000