Need to generate list based on specialized criteria

[scoobyroo]

There is a client table that contains client_code, client_name and client_status. There is also a matter table which contains matter_code, matter_name and matter_status. The two tables are linked through the field client_uno. The status field contains values such as CL for closed, op for open, ot for open for time, etc...) I need to show a list of clients that are not closed, but
all the matters for the client are closed except matter_code 00801 (if that matter exists for that client). Any advice on how best to do this?

 

[bborissov]

If you post some example data and want you want as a result from it, it will be better

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I have a feeling we need to use ALL and ANY here - I wish I remember Brad's blog on this topic - need to re-read.

 

[markros]

Here is a quick idea:

;with cte1 as (select Client_uNo, sum(case when Matter_Code <> '00801' and Matter_Status ='Closed' then 1 end) as ClosedMatters, count(*) as AllMatters, sum(case when Matter_Code = '00801' and Matter_Status <>'Closed') as OpenMatter00801 from Matters group by Client_uNo),
cte2 as (select * from cte1 where AllMatters = ClosedMatters + OpenMatter00801)

select Cl.* from Clients Cl inner join cte2 C2 on Cl.Client_uNo = C2.Client_uNo where Cl.Status <>'Cl'

 

[scoobyroo]

Here is sample data.

client_code client_name client_status matter_code matter_name matter_status
10000 client1 op 00001 matter1 CL
10000 client1 op 00002 matter2 CL
10000 client1 op 00003 matter3 CL
10000 client1 op 00004 matter4 CL
10000 client1 op 00005 matter5 CL
10000 client1 op 00006 matter6 CL
10000 client1 op 00007 matter7 CL
10000 client1 op 00801 matter801 REC


20000 client2 op 00001 matter1 OP
20000 client2 op 00002 matter2 CL
20000 client2 op 00003 matter3 OP
20000 client2 op 00004 matter4 CL
20000 client2 op 00005 matter5 OP
20000 client2 op 00006 matter6 CL
20000 client2 op 00007 matter7 CL
20000 client2 op 00801 matter801 REC


30000 client3 op 00001 matter1 CL
30000 client3 op 00002 matter2 CL
30000 client3 op 00003 matter3 OP
30000 client3 op 00004 matter4 CL
30000 client3 op 00005 matter5 OP
30000 client3 op 00006 matter6 CL
30000 client3 op 00007 matter7 CL
30000 client3 op 00801 matter801 CL


List would only show client1 since all matters have CL status except matter801.

 

[bborissov]

SELECT Clients.*
FROM Clients
INNER JOIN Clients Cl1
       ON  Clients.client_code = Cl1.client_code AND
           Cl1.matter_code     = '00801'             AND 
           Cl1. matter_status  <> 'CL'
LEFT  JOIN Clients Cl2
       ON  Clients.client_code = Cl1.client_code AND
           Cl1. matter_status  <> 'CL'
WHERE  Clients.matter_status  = 'CL' AND
       Cl2.client_code IS NULL

NOT TESTED!


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

I like the code I proposed - it should work just fine - just adjust for your field names.

 

[markros]

Ok, I re-read the blog

http://bradsruminations.blogspot.com/2009/08/all-any-and-some-three-stooges.html

and I think I changed my mind