SQL Query Help

  • Thread starter Thread starter Ed MacAlmon
  • Start date Start date
E

Ed MacAlmon

I'm running an adp project on an MSDE server. We have a program that
only entitles one of three services to be assigned to a client, but
staff too frequently enter more than one service for a client (thousands
of clients in the DB. For the life of me, I cannot figure out how to
write a select statement that will show me which clients have been
signed up for more than one of the allowable services. To date I have
been unable to work out code that finds known dual-service clients. The
DB is set up so that a Person table contains the demographic info and
the Activity History table contains the service info. They join on a
common ID. Can anyone suggest a general schema for this task? Thanks
for any help. --Ed
 
Use a group by following by an Having Count (*) >= 2; something like that:

SELECT Id, COUNT(*) AS Expr1
FROM Person
GROUP BY Id
HAVING (COUNT(*) >= 2)

Add the proper inner join to your history table.

S. L.
 
Sylvain said:
Use a group by following by an Having Count (*) >= 2; something like that:

SELECT Id, COUNT(*) AS Expr1
FROM Person
GROUP BY Id
HAVING (COUNT(*) >= 2)

Add the proper inner join to your history table.

S. L.
Click to expand...
Thanks, I'll give it a try.
 
Ed said:
Thanks, I'll give it a try.
Click to expand...
Sylvain, I ran what I believe was your suggestion and while it finds
more than one entry for any of the three particular services, A, B, or
C, it doesn't find any mixed pairs, e.g, B and C or A and B, etc. Am I
leaving something out? --Ed
 
No, you have missed nothing. It's me who have not fully understood what you
did exactly mean in your first message with the expressin « one allowable
service ».

If you want to test for mixed services while allowing more than one contract
by service, than you must reduce your combinaisons of 0, 1, ... N for each
service to be only 0 or 1; so that 1 remains 1 but N become 1. You can do
this very easily by using one of the following solutions:

1- Use a combination of the Exists (select * ... ) predicates:

.... From Person as P Where (
(Exists (select * from History H where P.ID=H.ID and H.Service='A')
and Exists (select * from History H where P.ID=H.ID and H.Service = 'B') OR
(... test for A and C here ...) OR
( ... test for 'B' and 'C' here ...)
)

Not a very fancy solution and will work only with a few pairs but very easy
to write down.


2- Use a combinaison of Group By and Union in a subquery:

SELECT Id, COUNT(*) AS Expr1
FROM
(
Select Id from Person as P where Exists (select * from History H where
P.Id=H.Id and H.Service='A')

Union ALL Select .... repeat for B, C, D, etc.
)

GROUP BY Id
HAVING (COUNT(*) >= 2)


3- Finally, the best solution would be to use a temporary table or a local
variable table and insert one record in this table for each person and each
type of service. Then you can easily count the number of services for each
person. This is usually the solution that should be used when the number of
services can be great of not known before hand.

Hope that will help.
S. L.
 
Back
Top