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

Question

Post reply

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

tolusokan

Newbie

Points: 0
More actions
March 29, 2016 at 7:30 pm

#399944

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

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply

pietlinden Newbie Points: 0 More actions · Answer 1

Welcome to SSC.

Since you're new, I'll go easy on you. =)

Thanks for the data, but it's not in a format we can use. If you could provide some CREATE TABLE and INSERT scripts so the folks here can run them and recreate your problem, you'll get a tested answer.

Help us to help you. Please read this article[/url] and you'll understand better how to create sample data so people can help.

(People just don't have a ton of time to set up your tables, so if you do that for them, answering is pretty easy...)

Thanks!

Pieter

Sachin Nandanwar Newbie Points: 0 More actions · Answer 2

tolusokan (3/29/2016)
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

Your sample data does not have patient id 2898568 but the expected output shows the result set for 2898568.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

tolusokan Newbie Points: 0 More actions · Answer 3

The 2 tables are just subsets of the larger set. They were just to illustrate what the original table is and what the expected result should look like.

Sachin Nandanwar Newbie Points: 0 More actions · Answer 4

Something like this...

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

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

tolusokan Newbie Points: 0 More actions · Answer 5

Hi Sachin. Thanks for the feedback. I tried to run this query but I kept getting a 'missing operator' message. I took the T out right before WHERE rid >= 8 and the 'IN' right after ORDER BY CPT_HCPS_PROC_CD but that didn't do it. Seems there might be something else missing.

Sachin Nandanwar Newbie Points: 0 More actions · Answer 6

Sorry..There was a silly typo in the earlier query.

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

There is an extra IN clause near the Order by .You need to remove that..

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server