15,306,585 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

Top Experts
Last 24hrsThis month
 Richard MacCutchan 125 OriginalGriff 70 Dave Kreskowiak 60 Greg Utas 45 Richard Deeming 40
 OriginalGriff 3,756 Richard MacCutchan 1,410 CPallini 1,015 Richard Deeming 773 Patrice T 705

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900