Hi,
I have these 2 tables. I want to update the testtbl3 with mid values with a join from testtbl4 mid values.
in testbl4, there are 2 different values for npi2. I want the mid with latest effectivedate from testtbl4. How can I achieve this? I think i will need some type of group by and max date. Tried but not able to figure it out.
Tables:
create table operationsdb.ops.testtbl3 (npi varchar(20), mid varchar(20))
insert into operationsdb.ops.testtbl3 values ('npi1', 'placeholder')
insert into operationsdb.ops.testtbl3 values ('npi2', 'placeholder')
insert into operationsdb.ops.testtbl3 values ('npi3', 'placeholder')
create table operationsdb.ops.testtbl4 (npi varchar(20), mid varchar(20), effectivedate date)
insert into operationsdb.ops.testtbl4 values ('npi1', '024894600','2018-01-17')
insert into operationsdb.ops.testtbl4 values ('npi2', '008520600','2012-04-19')
insert into operationsdb.ops.testtbl4 values ('npi2', '046082600','1987-05-31')
insert into operationsdb.ops.testtbl4 values ('npi3', '009595700','2013-09-16')
Expected result:
npi mid
npi1 024894600
npi2 008520600 -- there are 2 entries in testtbl4 for npi2 but i want the mid with latest date.
npi3 009595700
Thanks a lot! any help is greatly appreciated.