Sql query to retrieve rows grouped by specific values in a column

Transact-SQL
1

Sample_of_ExpectedResults.png1108×960 8.21 KB
claim_sample.png1323×960 32.3 KB

Hi,
I have been battling with this query for a while now. I attached a snippet of the raw file (claim) and also a sample of the expected results. The rule looks for claim lines that meet the following criteria:
• PATIENT_KEY is the same
• RENDERING_PROVIDER_KEY is the same
and CPT_HCPS_PROC_CD must have at least all 8 codes (82330, 82374,84520,84295, 84132,82947,82565,82435). Duplicates don't matter.

Any help is appreciated !

Thanks
ATS

2

Please try this sample query it may be helpful to you:

SELECT *
FROM   (SELECT *,
               Row_number()
                 OVER(
                   partition BY patient_key
                   ORDER BY CPT_HCPS_PROC_CD IN)rid
        FROM   YourTable
        WHERE  CPT_HCPS_PROC_CD IN( 82330, 82374,84520,84295, 84132,82947,82565,82435 ))T
WHERE  rid >= 8
3 
SELECT mtn.*
FROM (
    SELECT PATIENT_KEY, RENDERING_PROVIDER_KEY
    FROM main_table_name
    WHERE CPT_HCPS_PROC_CD IN ('82330', '82374', '82435', '82565', '82947', '84132', '84295', '84520')
    GROUP BY PATIENT_KEY, RENDERING_PROVIDER_KEY
    HAVING COUNT(DISTINCT CPT_HCPS_PROC_CD) = 8
) AS pat_matches
INNER JOIN main_table_name mtn ON
    mtn.PATIENT_KEY = pat_matches.PATIENT_KEY AND
    mtn.RENDERING_PROVIDER_KEY = pat_matches.RENDERING_PROVIDER_KEY
--if you want to list only those 8 codes; if you want all rows/codes, leave this line commented out.
--WHERE mtn.CPT_HCPS_PROC_CD IN (82330, 82374, 82435, 82565, 82947, 84132, 84295, 84520)
4

Thanks Scott. I tried the query but for some reason I kept getting a Syntax Error (missing operator) in query expression 'COUNT(DISTINCT CPT_HCPS_PROC_CD) = 8'. Cant seem to pinpoint the issue

5

Scott's solution works perfectly.

6

@ats00, please show your whole query. Sometime the error message fails a ways after the problem.

7

Make sure that you have an alias name, "AS alias_name", after the derived table:

SELECT mtn.*
FROM (
    SELECT PATIENT_KEY, RENDERING_PROVIDER_KEY
    FROM main_table_name
    WHERE CPT_HCPS_PROC_CD IN ('82330', '82374', '82435', '82565', '82947', '84132', '84295', '84520')
    GROUP BY PATIENT_KEY, RENDERING_PROVIDER_KEY
    HAVING COUNT(DISTINCT CPT_HCPS_PROC_CD) = 8

) AS pat_matches --<< this is critical in SQL Server

8

Is that a SQL Server error message? or an Access one?

9

It's actually an Access error message. Seems I'm having some formatting issues with Access.

10

This is primarily a Microsoft SQL Server forum, so it might be that folk here won't be familiar enough with Access to be able to solve your problem.

11

Yes :slight_smile: I realize that. I appreciate all the fantastic help I've got thus far. You guys are awesome.

12

Yeah, sorry, I have no clue if my SQL code will work in Access or not.

13

Upgrade to SQL Server? You-know-you-want-to :chug: