September 24, 2014 at 11:30 am
I have an SQL issue that I cant solve and I need some help with it please. The attached file is an extract from a sql server data warehouse and contains Last Name, first name, id, NPI and so on. There are many duplications in here such as the name, NPI, City etc. However, one or two fields of the address are different which is OK. The id field is the actual only Unique field.
This table needs to join to another table with financial information and is linked on the NPI. This is the only field that can be joined.
As you can see, there are several rows of what appears to be the same person with the same NPI. What I need to do is when I link to the Finacials table, is to only bring back the 1st record of each person from this Persons table. Is there anything in SQL that will do this.. say
id 1, then id 87, then id 158 etc etc. So, the 1st record from a new id? There are over 1m rows so manually adding the id is not do-able.
Many Thanks in advance
September 24, 2014 at 1:54 pm
goldstone.david (9/24/2014)
I have an SQL issue that I cant solve and I need some help with it please. The attached file is an extract from a sql server data warehouse and contains Last Name, first name, id, NPI and so on. There are many duplications in here such as the name, NPI, City etc. However, one or two fields of the address are different which is OK. The id field is the actual only Unique field.This table needs to join to another table with financial information and is linked on the NPI. This is the only field that can be joined.
As you can see, there are several rows of what appears to be the same person with the same NPI. What I need to do is when I link to the Finacials table, is to only bring back the 1st record of each person from this Persons table. Is there anything in SQL that will do this.. say
id 1, then id 87, then id 158 etc etc. So, the 1st record from a new id? There are over 1m rows so manually adding the id is not do-able.
Many Thanks in advance
Not certain I got it right but here is something in the direction of a solution
😎
;WITH BASE_DATA AS (SELECT * FROM (VALUES
('1','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','500 MEMORIAL AVE','302','N','CUMBERLAND','MD')
,('2','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','902 SETON DR',' ','N','CUMBERLAND','MD')
,('3','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','902 SETON DR','301','N','CUMBERLAND','MD')
,('4','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','12502 WILLOWBROOK RD','280','N','CUMBERLAND','MD')
,('5','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','900 SETON DR',' ','N','CUMBERLAND','MD')
,('6','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','625 KENT AVE',' ','N','CUMBERLAND','MD')
,('7','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','904 SETON DR','204','N','CUMBERLAND','MD')
,('8','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','12502 WILLOWBROOK RD','580','N','CUMBERLAND','MD')
,('9','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','12502 WILLOWBROOK RD',' ','N','CUMBERLAND','MD')
,('10','1003000126','7517003643','I20091005000100','Thomas','Bob','M','INTERNAL MEDICINE','WESTERN MARYLAND HEALTH SYSTEM CORPORATION','7113900655','74','600 MEMORIAL AVE',' ','Y','CUMBERLAND','MD')
,('87','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','600 ZEAGLER DR',' ','N','PALATKA','FL')
,('88','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','2161 KINGSLEY AVE',' ','Y','ORANGE PARK','FL')
,('89','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','1375 ROBERTS DR',' ','Y','JACKSONVILLE BEACH','FL')
,('90','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','2161 KINGSLEY AVE',' ','Y','ORANGE PARK','FL')
,('91','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','1235 SAN MARCO BLVD','202','N','JACKSONVILLE','FL')
,('92','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','600 ZEAGLER DR','1','N','PALATKA','FL')
,('93','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','1375 ROBERTS DR',' ','Y','JACKSONVILLE BEACH','FL')
,('94','1003001462','8022273648','I20120705000632','Testing','Person','M','RADIATION ONCOLOGY','INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC','1355308768','64','1235 SAN MARCO BLVD',' ','N','JACKSONVILLE','FL')
,('158','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST I',' ','N','TERRE HAUTE','IN')
,('159','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST A',' ','N','TERRE HAUTE','IN')
,('160','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST D',' ','N','TERRE HAUTE','IN')
,('161','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST G',' ','N','TERRE HAUTE','IN')
,('162','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST L',' ','N','TERRE HAUTE','IN')
,('163','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST C',' ','N','TERRE HAUTE','IN')
,('164','1003005208','1850442039','I20090629000456','Anyone','Mark','F','PHYSICAL THERAPIST','PROVIDENCE MEDICAL GROUP, LLC','9335135995','45','2723 S 7TH ST M',' ','N','TERRE HAUTE','IN')
,('183','1003006248','4789757147','I20080717000337','Man','Mr','M','OTOLARYNGOLOGY','CHILDRENS SURGICAL ASSOCIATES LTD','9739083494','44','34 CIVIC CTR BLVD',' ','N','PHILADELPHIA','PA')
,('184','1003006248','4789757147','I20080717000337','Man','Mr','M','OTOLARYNGOLOGY','CHILDRENS SURGICAL ASSOCIATES LTD','9739083494','44','34 TH & CIVIC CTR BLVD',' ','N','PHILADELPHIA','PA')
,('185','1003006248','4789757147','I20080717000337','Man','Mr','M','OTOLARYNGOLOGY','CHILDRENS SURGICAL ASSOCIATES LTD','9739083494','44','34 CIVIC CTR BLVD','FIRST FLOOR','N','PHILADELPHIA','PA')
,('186','1003006248','4789757147','I20080717000337','Man','Mr','M','OTOLARYNGOLOGY','CHILDRENS SURGICAL ASSOCIATES LTD','9739083494','44','34 TH AND CIVIC CTR BLVD',' ','N','PHILADELPHIA','PA')
,('187','1003006248','4789757147','I20080717000337','Man','Mr','M','OTOLARYNGOLOGY','CHILDRENS SURGICAL ASSOCIATES LTD','9739083494','44','34 TH & CIVIC CTR BLVD','FIRST FLOOR WOOD','N','PHILADELPHIA','PA')
,('188','1003006248','4789757147','I20080717000337','Man','Mr','M','OTOLARYNGOLOGY','CHILDRENS SURGICAL ASSOCIATES LTD','9739083494','44','34 TH & CIVIC CTR BLVD','FIRST FLOOR WOOD','N','PHILADELPHIA','PA')
,('203','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','2525 S MICHIGAN AVE',' ','N','CHICAGO','IL')
,('204','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','2010 S ARLINGTON HEIGHTS RD',' ','N','ARLINGTON HEIGHTS','IL')
,('205','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','901 W KIRCHHOFF RD',' ','N','ARLINGTON HEIGHTS','IL')
,('206','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','2850 S MICHIGAN AVE',' ','N','CHICAGO','IL')
,('207','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','5825 W CERMAK RD',' ','N','CICERO','IL')
,('208','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','333 N HAMMES AVE','107','N','JOLIET','IL')
,('209','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','1919 W TAYLOR ST',' ','N','CHICAGO','IL')
,('210','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','1801 W TAYLOR ST',' ','Y','CHICAGO','IL')
,('211','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','100 N RIVER RD',' ','N','DES PLAINES','IL')
,('212','1003006701','4688854995','I20110203000530','Human','Mr','M','PSYCHIATRY','THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS','3072422716','643','2233 W DIVISION ST',' ','N','CHICAGO','IL')
) AS X (id,NPI,PAC_ID,ProfessionalEnrollmentID,LastName,FirstName,Gender,PrimarySpecialty,Organization,[Another ID],NumberOfGroupPracticeMembers,Line1StreetAddress,Line2StreetAddress,MarkerOfAddressLine2Suppression,City,State)
)
SELECT * FROM (
SELECT
ROW_NUMBER() OVER
(
PARTITION BY NPI
ORDER BY id DESC
) AS RID
,* FROM BASE_DATA ) AS X WHERE X.RID = 1
Results
RID id NPI PAC_ID ProfessionalEnrollmentID LastName FirstName Gender PrimarySpecialty Organization Another ID NumberOfGroupPracticeMembers Line1StreetAddress Line2StreetAddress MarkerOfAddressLine2Suppression City State
-------------------- ---- ---------- ---------- ------------------------ -------- --------- ------ ------------------ --------------------------------------------------- ---------- ---------------------------- --------------------------- ------------------ ------------------------------- ------------------ -----
1 9 1003000126 7517003643 I20091005000100 Thomas Bob M INTERNAL MEDICINE WESTERN MARYLAND HEALTH SYSTEM CORPORATION 7113900655 74 12502 WILLOWBROOK RD N CUMBERLAND MD
1 94 1003001462 8022273648 I20120705000632 Testing Person M RADIATION ONCOLOGY INTEGRATED COMMUNITY ONCOLOGY NETWORK,LLC 1355308768 64 1235 SAN MARCO BLVD N JACKSONVILLE FL
1 164 1003005208 1850442039 I20090629000456 Anyone Mark F PHYSICAL THERAPIST PROVIDENCE MEDICAL GROUP, LLC 9335135995 45 2723 S 7TH ST M N TERRE HAUTE IN
1 188 1003006248 4789757147 I20080717000337 Man Mr M OTOLARYNGOLOGY CHILDRENS SURGICAL ASSOCIATES LTD 9739083494 44 34 TH & CIVIC CTR BLVD FIRST FLOOR WOOD N PHILADELPHIA PA
1 212 1003006701 4688854995 I20110203000530 Human Mr M PSYCHIATRY THE BOARD OF TRUSTEES OF THE UNIVERSITY OF ILLINOIS 3072422716 643 2233 W DIVISION ST N CHICAGO IL
September 24, 2014 at 3:09 pm
Wow, thank you. Would I need to enter all the data from the table, ie. 1m rows? or can I just reference the table?
September 24, 2014 at 3:16 pm
goldstone.david (9/24/2014)
Wow, thank you. Would I need to enter all the data from the table, ie. 1m rows? or can I just reference the table?
Just replace the BASE_TABLE CTE with your table
😎
September 24, 2014 at 3:19 pm
So basically I dont need to enter all the red.. I just enter... the below? It will be impossible to enter all the values in red.
SELECT * FROM (
SELECT
ROW_NUMBER() OVER
(
PARTITION BY NPI
ORDER BY id DESC
) AS RID
,* FROM PhysiciansTable ) AS X WHERE X.RID = 1
September 24, 2014 at 3:23 pm
goldstone.david (9/24/2014)
So basically I dont need to enter all the red.. I just enter... the below? It will be impossible to enter all the values in red.SELECT * FROM (
SELECT
ROW_NUMBER() OVER
(
PARTITION BY NPI
ORDER BY id DESC
) AS RID
,* FROM PhysiciansTable ) AS X WHERE X.RID = 1
Yes, the "red" is just a substitute for your data which I do not have access to;-)
😎
September 24, 2014 at 3:26 pm
Thank you. I shall try this tomorrow. I will report back and let you know.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply