Help with a correlated subquery
-
Hi,
Here is how the data looks like:
Case.........Client........Age.........Inc Code
------------------------------------------------
123...........551...........65...........Z
123...........552...........32...........A
123...........553...........35...........Y
125...........598...........68..........Y
125...........599...........33..........A
127..........600............22..........A
127..........600............22..........X
Conditions:
-A client is a valid client if Age >= 60 OR Income Code In List (A,B).
-A case should not be counted if there is an invalid client.
-A client is valid is valid if it exists multiple times with atleast one valid Income code.
Based on the above conditions, in Case# 123 the first 2 rows are valid but the third row is an invalid client. Hence this case should not be counted. Case# 125 has two rows and both are valid so this case should be counted. Case# 127 should be counted because it satisfies rule#3.
I created a NOT EXISTS subquery as:
Code:
NOT EXISTS
(
Select 1
From table b1
where table.CASE_NUMBER=b1.CASE_NUMBER AND
Age < 60 AND
table.INCOME_CODE NOT IN ('A','B')
)
But I am still getting the first case back
Thanks for the expert advice.
-
I'm not sure I get the requirements... Can you post the expected results of the query with the presented data?
-
Thanks for your reply Remi. The expected result in the above example should be:
Case.........Client........Age.........Inc Code
------------------------------------------------
125...........598...........68..........Y
125...........599...........33..........A
127..........600............22..........A
127..........600............22..........X
Case#125 is counted because both the clients are valid clients. The first client is valid bacause of the age and second client is valid because of the income code.
Case#125, the client is valid bacause it has one of the valid codes.
Hope this helps.
THanks,
Kash
-
So your first query is to return a list of valid clients - and then you need to join that with 'table'.
To select valid clients:
select distinct t.client from table t where t.age > 60 or t.IncCode = 'A' or t.IncCode = 'B'
Now do the join
select t1.* from table t1 join
(select distinct t.client from table t where t.age > 60 or t.IncCode = 'A' or t.IncCode = 'B') t2
on t1.client = t2.client
Regards
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I tried the above, but it seemed to return more than you wanted.
I think this will work - the tricky part is rule #3, which is the "exists" clause (the "not in" clause is rule #2)
select * from clients c
where caseid not in
(select caseid from clients c2
where c2.age < 60 and c2.inccode not in ('a','b')
AND not exists
(select * from clients c3
where c3.age <= 60 and c3.inccode in ('a','b')
and c3.clientid = c2.clientid))
BTW, I tweaked the names a bit as follows:
CREATE TABLE [Clients]
([CaseID] [tinyint],
[ClientID] [smallint],
[Age] [tinyint],
[IncCode] [char] (1))
-
Thanks for your replies. Paul you are absolutely right that the tricky part is rule#3. I am having difficulty with this rule. I'll try your query and will let you know. Thanks again for all the help you all provided.
I really appreciate it.
Kash
-
Don't thank me yet - I noticed a typo in the first attempt - this is more like it ...for now 😉
select * from clients c
where caseid not in
(select caseid from clients c2
where c2.age < 60 and c2.inccode not in ('a','b')
AND clientid not in
(select clientid from clients c3
where c3.age >= 60 or c3.inccode in ('a','b')
and c3.clientid = c2.clientid))
-
Paul,
Thanks for your reply again. Because of some load issues I was not been able to try the query out as yet but it makes sense logically and it should work. I'll post the outcomes as soon as I get this to run
Thanks,
Kash
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply