Select records that dont exist based off of max and min date ranges.
-
I have 2 tables;
T1 has [services_rendered], [date], [client_id]
T2 has [service_period], [client_id]
I am trying to find records in each table that are considered invalid based off of the service_period. The services_rendered must fall within the service_period time frame to be valid.
Logically, I am thinking I need to get the min(service_date) and max(service_date) in T1 for each client and check to see if the client_id esxists in T2 and if it falls within the range. If not, I want to select it.
I can't quite figure out how to do this and the only way I can think of is with a loop or I guess the SQL cursor which I am finding out should be avoided at all costs.
Any hints?
-
If you take the min and make from T, what happens if the client has two records?
Also, it would be helpful if you could provide some test data. I believe we will be able to solve this problem without the use of recursion.
-
-
Please post sample data and desired results.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
-
I'd try using a derived table with a BETWEEN clause to identify all those that are within the service period and then left join that back to the table again to identify those rows that are outside the service period. Without structure & data, it'd be hard to supply more detail.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Maybe this?
select T1.client_id,T1.services_rendered,T1.datefrom T1
where not exists (select T2.client_id
from T2
where T2.client_id=T1.client_id
group by T2.client_id
having T1.date between min(T2.services_period) and max(T2.services_period))
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thank you very much everyone!
Mark, your query looks like it will do the job. I am going to read up on the group by / having clause. I would be interested in hearing your explanation of the logic. I am a rookie and this one is confusing to me (amongst others)
Sorry I didn't post any test data, the actual data isn't that easy to disseminate. (field names aren't as easy to identify with what they represent etc...).
There are many rows in T2 for the same client and this looks like it will do the trick.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply