More Efficient SQL Query to get Distinct List

[mrmmickle1]

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....

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.

 

[james_lankford]

edit: never mind
I see an Accession ID can appear more than once in the query

 

[james_lankford]

try this

SELECT DISTINCT 
  REALBIGQUERY.[Accession ID]
from
(
  
    SELECT 
      qryGCodeUnder300Detail.[Accession ID], 
      qryGCodeUnder300Detail.[Procedure Code] as [pcode],
      count(*) as number_of_records
    FROM 
      qryGCodeUnder300Detail 
    GROUP BY 
      qryGCodeUnder300Detail.[Accession ID],
      qryGCodeUnder300Detail.[Procedure Code]
) as REALBIGQUERY 
where 
(
  (
    pcode in ( '88141', '88175' )
    and
    number_of_records > 0
  )
  or 
  (
    pcode in ( '111', '222' )
    and
    number_of_records > 0
  )
)

you might even be able to shorter and do this

SELECT DISTINCT 
  REALBIGQUERY.[Accession ID]
from
(
  
    SELECT 
      qryGCodeUnder300Detail.[Accession ID], 
      qryGCodeUnder300Detail.[Procedure Code] as [pcode]
    FROM 
      qryGCodeUnder300Detail 
    GROUP BY 
      qryGCodeUnder300Detail.[Accession ID],
      qryGCodeUnder300Detail.[Procedure Code]
) as REALBIGQUERY 
where 
(
  (
    pcode in ( '88141', '88175' )
  )
  or 
  (
    pcode in ( '111', '222' )
  )
)

 

[mrmmickle1]

james_lankford,

Thank you for the help. I just tried the two queries you provided. Unfortunately they both pulled in these two values that do not meet the criteria:

Excel 2013

	A	B	C	D	E	F
49	48	CY1500395	TRIS	88142	1	25.13
50	49	CY1500395	TRIS	88141	1	30.07
51	50	CY1500395	TRIS	*N/A*	0	0.00
52	51	CY1500396	GRW	88142	0	0.00
53	52	CY1500396	GRW	88141	0	0.00
54	53	CY1500396	GRW	*N/A*	0	0.00
55	54	CY1500396	GRW	*N/M*	0	0.00

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

qryGcodeUnder300Detail​

This looks promising though. If it would be helpful for me to put the values in another table called tblCriteria. I could do that:

Excel 2013

	A	B
1	GCode1	GCode2
2	88141	88175
3	111	222

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

tblCriteria​

 

[james_lankford]

take this

SELECT 
  qryGCodeUnder300Detail.[Accession ID], 
  qryGCodeUnder300Detail.[Procedure Code] as [pcode],
  count(*) as number_of_records
FROM 
  qryGCodeUnder300Detail 
GROUP BY 
  qryGCodeUnder300Detail.[Accession ID],
  qryGCodeUnder300Detail.[Procedure Code]

and save as
REALBIGQUERY

then try this

SELECT 
  REALBIGQUERY.[Accession ID], 
  REALBIGQUERY.pcode, 
  REALBIGQUERY_1.pcode,
  REALBIGQUERY.number_of_records,
  REALBIGQUERY_1.number_of_records
FROM 
(
  REALBIGQUERY 
  INNER JOIN 
  REALBIGQUERY AS REALBIGQUERY_1 
    ON 
    REALBIGQUERY.[Accession ID] = REALBIGQUERY_1.[Accession ID]
) 
INNER JOIN 
tblCriteria 
  ON 
  (
    REALBIGQUERY_1.pcode = tblCriteria.GCode2
  ) 
  AND 
  (
    REALBIGQUERY.pcode = tblCriteria.GCode1
  )
WHERE 
(
  (
    REALBIGQUERY.pcode <> REALBIGQUERY_1.pcode 
  )
)

but with 500000 records it still may take forever

 

[mrmmickle1]

This ended up working great! Thanks for the help. Only took about 3 or 4 minutes, which is alot faster then I expected.