January 7, 2016 at 12:28 pm
Folks
See attached file for DATA after running the update statement below.
WHAT I NEED: The provider_in table has a column
by the name "ENROLL_DATE" ( datetime ).
I want to update the table PARTB with the PROV_NBR that has the earliest
ENROLL_DATE in the table dbo.PROVIDER_IN.
Right now , it is just randomly picking any PROV_NBR
from the dbo.PROVIDER_IN table belonging to a given ClmBlgPrvdrNpiNum
( I mean for any given ClmBlgPrvdrNpiNum there can be multiple PROV_NBR in the dbo.PROVIDER_IN table )
UPDATE t
Set
PROV_NBR = x.PROV_NBR
FROM
PARTB t
inner join
dbo.PROVIDER_IN x on ( x.PROV_NPI = t.ClmBlgPrvdrNpiNum )
select * FROM PARTB
Select * FROM PROVIDER_IN WHERE PROV_NPI = '1346255072'
January 7, 2016 at 12:40 pm
Here are 2 options. Analyse them, test them and choose.
UPDATE t
SET PROV_NBR = x.PROV_NBR
FROM PARTB t
CROSS APPLY ( SELECT TOP (1) p.PROV_NBR
FROM dbo.PROVIDER_IN p
WHERE p.PROV_NPI = t.ClmBlgPrvdrNpiNum
ORDER BY p.ENROLL_DATE) x;
WITH CTE AS(
SELECT p.PROV_NBR, p.PROV_NPI,
ROW_NUMBER() OVER( PARTITION BY p.PROV_NPI ORDER BY p.ENROLL_DATE) rn
FROM dbo.PROVIDER_IN p
)
UPDATE t
SET PROV_NBR = x.PROV_NBR
FROM PARTB t
JOIN CTE x ON x.PROV_NPI = t.ClmBlgPrvdrNpiNum AND x.rn = 1;
January 7, 2016 at 4:09 pm
If every row in PARTB has at least one match in PROVIDER_IN, you can also use:
UPDATE dbo.PARTB
SET PROV_NBR = ( SELECT TOP (1) p.PROV_NBR
FROM dbo.PROVIDER_IN p
WHERE p.PROV_NPI = PARTB.ClmBlgPrvdrNpiNum
ORDER BY p.ENROLL_DATE);
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply