How to combine tables to get new field with max value of both fileds?

[iren]

I have MED & PHARM tables
Med table has ID and end_dt
Pharm table has ID and First_Serv_dt.
I extracted max dates from both tables in step1 and 2

How can I combine them to get Last_clm_dt
which extract maximum date from both date fields?


Medical:&tmpschema..Hosp_w_lst_clm
Pharmacy:&tmpschema..Hosp_w_lst_PhaclmI need to build

Problem is also that date tables have differnt names.....

Could you please give me a hand with code for step 3? This
code is run through SAS environment against DB2 DW.
Thank you in advance!

Iren


EXECUTE(
create table &tmpschema..Hosp_w_lst_clm
( Id char (29)
,End_Dt date
not logged initially
) by Mconnect;
EXECUTE(
insert into &tmpschema..Hosp_w_lst_clm
select distinct Id
, max(End_dt)
from &tmpschema..Hosp_w_clmdt
group by iD
) by Mconnect;



EXECUTE(
create table &tmpschema..Hosp_w_lst_Phaclm
( Id char (29)
,First_SRV_DT date
) by Mconnect;

EXECUTE(
insert into &tmpschema..Hosp_w_lst_Phaclm
select distinct
Id
,max(First_SRV_DT)
from &clmschema..phasrv_detail
group by ID
) by Mconnect;



/*Step 3 Keep max date OF Medical OR RX DATE*/

EXECUTE(
create table &tmpschema..Hosp_w_lst_clm_max
( Id char (29)
,last_clm_dt date
not logged initially
) by Mconnect; EXECUTE(
insert into &tmpschema..Hosp_w_lst_clm_max
select distinct

from ..........

 

[blom0344]

SELECT TEMP.U_ID,TEMP.MAX(U_DATE) FROM
(SELECT ID AS U_ID,MAX(END_DT) AS U_DATE FROM
 MED
 GROUP BY ID
 UNION
 SELECT ID AS U_ID,MAX(FIRST_SERV_DT) AS U_DATE FROM
 PHARM
 GROUP BY ID) TEMP

wil yield the highest date over both tables for each ID

There is never any need to add a distinct for an aggregate like you use them

Hope I got your requirement right , otherwise let us know..

Ties Blom