Date:         Thu, 21 Aug 2008 22:23:21 -0400
Reply-To:     Sigurd Hermansen <SigurdHermansen@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <SigurdHermansen@WESTAT.COM>
Subject:      Re: How to get max value from two tables in SQL?
Comments: To: "irinfigvam@yahoo.com" <irinfigvam@yahoo.com>
In-Reply-To:  <460863.11056.qm@web51602.mail.re2.yahoo.com>
Content-Type: text/plain; charset="us-ascii"

Here you are looking for the max() of two different attributes, a different maximum than a maximum of an attribute and a different function:

         insert into &tmpschema..Hosp_w_lst_clm_max
           select distinct coalesce( max(t1.Id,t2.Id) as Id,max(t1.End_Dt,t2.First_SRV_DT) as last_clm_date
           from &tmpschema..Hosp_w_lst_clm as t1 inner join &tmpschema..Hosp_w_lst_Phaclm as t2\
                                                     on t1.Id=t2.Id
         ;
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Irin later
Sent: Wednesday, August 20, 2008 7:20 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: How to get max value from two tables in SQL?


I am extracting max end_dt for members in Medical (Step1) and max First_srv_dt in Pharmacy (Step2). Medical:&tmpschema..Hosp_w_lst_clm
Pharmacy:&tmpschema..Hosp_w_lst_Phaclm
I need to build a combined table for members  &tmpschema..Hosp_w_lst_clm_max with last_clm_dt= which should be max among max end_dt from Medical table and max First_srv_dt from Farmacy table. 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! Irin
/*------------------------------------------------------
   Step 1 This step gets Medical ID & max srv_dt
  ---------------------------------------------------*/
        EXECUTE(
        create table &tmpschema..Hosp_w_lst_clm
                                    ( Id          char    (29)
                                     ,End_Dt      date
                                     )
               in &onetblspc
               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;
/*------------------------------------------
   Step 2 - This step gets ID for phasrv_detail  & max  RX: first_srv_dt
  ---------------------------------------------*/
        EXECUTE(
        create table &tmpschema..Hosp_w_lst_Phaclm
                                    ( Id            char    (29)
                                     ,First_SRV_DT  date
                                     )
               in &onetblspc
               not logged initially
            ) 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

                                     )
               in &onetblspc
               not logged initially
            ) by Mconnect;
      EXECUTE(
         insert into &tmpschema..Hosp_w_lst_clm_max
           select distinct

           from ..........