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.
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 nrFROM Data where RunDate > GETDATE()-12)as a where a.nr = 1 and RunDate < GETDATE()-6and 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 Datawhere rundate > getdate()-12and nr = 1and clientid = @clientidgroup by <whatever data1, data2?>having max(rundate) < getdate()-6EDIT:for all clients at once just add clientid to select and group byBe One with the OptimizerTG |
|
|
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. |
|
|
|
|
|
|
|