15,306,676 members
See more:
Trying to write an optimized query because table is huge.

Lets see you have patients and charges

Acctnumber |ChargeCode
A |103567
A |105678
A |103567
A |105678
B |345356
B |105678
B |234035
C |105678
D |403567
D |505678

I need the query to pick all the accounts that only have charges starting with 10. So the result would be
Acctnumber |ChargeCode
A |103567
A |105678
A |103567
A |105678
C |105678

The B account also has 10 charge code but it has other codes as well. We only want 10 codes.

What I have tried:

I tried writing the query through in (chargecode10) and not exist but that's a slow way to do it.

Let me know if someone knows a better way
Posted
Updated 10-Aug-18 6:34am

## Solution 1

Is ChargeCode a string?

SELECT Acctnumber, ChargeCode FROM PatientCharges WHERE ChargeCode LIKE '10%';

If it's a number

SELECT Acctnumber, ChargeCode FROM PatientCharges WHERE LTRIM(STR(ChargeCode,10)) LIKE '10%';
v2
AZ93 10-Aug-18 15:51pm

That would also pull the B Account because B has 10 charge as well. We only have to pick accounts with only 10% charges and none else.
the result should only be A and C
Mike V Baker 10-Aug-18 21:20pm

SELECT Acctnumber, ChargeCode FROM PatientCharges WHERE ChargeCode LIKE '10%' AND AcctNumber NOT IN (SELECT DISTINT AcctNumber FROM PatientCharges WHERE ChargeCode NOT LIKE '10%');

?? Not sure about how long this will take. How many records we talking about? Is ChargeCode indexed?
AZ93 13-Aug-18 17:59pm

That is exactly what I was doing but we are talking about million records