15,305,613 members
0.00/5 (No votes)
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
Comments
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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Top Experts
Last 24hrsThis month
 OriginalGriff 205 Richard Deeming 115 CHill60 75 CPallini 60 Rick York 40
 OriginalGriff 3,676 Richard MacCutchan 1,275 CPallini 1,015 Richard Deeming 733 Patrice T 695

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