mrmmickle1
Well-known Member
- Joined
- May 11, 2012
- Messages
- 2,461
Currently I have data that has a few fields. The field that I am interested in is called Procedure Code. I am looking for a way to determine all of the Accession ID's that contain two distinct Procedure Codes. For example if I have an Accession that has procedure codes "88141" and "88175" I would like to include it in my query. THe issue is that I need to look at multiple combinations. In the below query I have two combinations:
88141 & 88175
111 & 222
My query works fine for this small dataset, THe issue is that I actually have a total of 546 combinations of codes and 5,000,000 records. I have been able to get my method to work on a total of 4 combinations of codes, however, it took 7 minutes to run. Is there a more efficient way to determine which Acession ID's contain combinations of these Procedure Codes? The reason I use the Distinct is because there is also the possiblity of overlap....
Any assistance in getting my query more efficient would be much appreciated. Thank you for looking into my situation.
88141 & 88175
111 & 222
My query works fine for this small dataset, THe issue is that I actually have a total of 546 combinations of codes and 5,000,000 records. I have been able to get my method to work on a total of 4 combinations of codes, however, it took 7 minutes to run. Is there a more efficient way to determine which Acession ID's contain combinations of these Procedure Codes? The reason I use the Distinct is because there is also the possiblity of overlap....
Code:
SELECT DISTINCT REALBIGQUERY.[Accession ID]
FROM
(SELECT CALCQuery.*
FROM (SELECT qryGCodeUnder300Detail.[Accession ID], Sum(IIf([qryGCodeUnder300Detail].[Procedure Code]="88141",1,0)) AS FIRSTGCODE, Sum(IIf([qryGCodeUnder300Detail].[Procedure Code]="88175",1,0)) AS SECONDGCODE FROM qryGCodeUnder300Detail
GROUP BY qryGCodeUnder300Detail.[Accession ID]) AS CALCQuery
WHERE FIRSTGCODE >= 1 AND SECONDGCODE >= 1
UNION
SELECT CALCQuery.*
FROM (SELECT qryGCodeUnder300Detail.[Accession ID], Sum(IIf([qryGCodeUnder300Detail].[Procedure Code]="111",1,0)) AS FIRSTGCODE, Sum(IIf([qryGCodeUnder300Detail].[Procedure Code]="222",1,0)) AS SECONDGCODE FROM qryGCodeUnder300Detail
GROUP BY qryGCodeUnder300Detail.[Accession ID]) AS CALCQuery
WHERE FIRSTGCODE >= 1 AND SECONDGCODE >= 1) AS REALBIGQUERY;
Excel 2012 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | ID | Accession ID | Primary Payor ID | Procedure Code | Units Paid | Posted Paid Amount | ||
2 | 1 | CY1500389 | BSSC | *N/M* | 0 | 0.00 | ||
3 | 2 | CY1500389 | BSSC | 88141 | 0 | 0.00 | ||
4 | 3 | CY1500389 | BSSC | 88175 | 0 | 0.00 | ||
5 | 4 | CY1500389 | BSSC | 88141 | 0 | 0.00 | ||
6 | 5 | CY1500389 | BSSC | 88175 | 0 | 0.00 | ||
7 | 6 | CY1500389 | BSSC | *N/M* | 0 | 0.00 | ||
8 | 7 | CY1500389A | BSSC | *N/M* | 0 | 0.00 | ||
9 | 8 | CY1500389A | BSSC | 87624 | 0 | 0.00 | ||
10 | 9 | CY1500390 | BSSC | 88141 | 0 | 0.00 | ||
11 | 10 | CY1500390 | BSSC | 88141 | 0 | 0.00 | ||
12 | 11 | CY1500390 | BSSC | 111 | 0 | 0.00 | ||
13 | 12 | CY1500390 | BSSC | 88175 | 0 | 0.00 | ||
14 | 13 | CY1500390 | BSSC | 222 | 0 | 0.00 | ||
15 | 14 | CY1500390 | BSSC | 88175 | 0 | 0.00 | ||
16 | 15 | CY1500390A | BSSC | 87625 | 0 | 0.00 | ||
17 | 16 | CY1500390A | BSSC | 87624 | 0 | 0.00 | ||
18 | 17 | CY1500390A | BSSC | *N/A* | 0 | 0.00 | ||
19 | 18 | CY1500391 | ATCSC | *N/A* | 0 | 0.00 | ||
20 | 19 | CY1500391 | ATCSC | 88142 | 1 | 21.67 | ||
21 | 20 | CY1500391A | ATCSC | 88141 | 0 | 0.00 | ||
22 | 21 | CY1500391A | ATCSC | 88175 | 1 | 38.21 | ||
23 | 22 | CY1500392 | ABS | 88142 | 0 | 0.00 | ||
qryGcodeUnder300Detail |
Any assistance in getting my query more efficient would be much appreciated. Thank you for looking into my situation.