Grouping in SQL - accounts and charges

Question

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 10-Aug-18 5:50am

AZ93

Updated 10-Aug-18 6:34am

Add a Solution

1 solution

Add a Solution

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

Mike V Baker · Answer 1 · 2018-08-10T06:34:00

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%';

Posted 10-Aug-18 6:34am

Mike V Baker

Updated 10-Aug-18 6:36am

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

Grouping in SQL - accounts and charges

1 solution

Solution 1

Add your solution here

Preview 0

Grouping in SQL - accounts and charges

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us