You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Select most recent (as on current date) active address among HOME, MAIL

2 replies [Last post]
simrose's picture
User offline. Last seen 6 years 32 weeks ago. Offline
Joined: 08/01/2008
Posts: 33

Need a small help in SQL

I need a SQL in which it will return the most active date address between HOME and MAIL address

Like if Address table has two rows

01/01/2009 HOME
02/02/2009 MAIL
It will return most recent address i.e MAIL as is most recent address

But if both MAIL and HOME address are of same effective date it should return just MAIL address.

02/02/2009 HOME
02/02/2009 MAIL
Result should be MAIL address

Can any one please help me with that and I am on DB2 how to return mail address when Effective dates are same ?

SQL :

SELECT DISTINCT A.EMPLID
 ,A.EFFDT
,A.ADDRESS_TYPE
,A.ADDRESS1
 , A.ADDRESS2
 , A.ADDRESS3
 , A.ADDRESS4
 , A.CITY
 , A.STATE
 , A.POSTAL
 ,A.COUNTRY
FROM PS_ADDRESSES A
 WHERE  A.EFF_STATUS='A'
   AND  (A.ADDRESS_TYPE = 'HOME' OR  A.ADDRESS_TYPE = 'MAIL')
   AND A.EFFDT= (
 SELECT MAX(EFFDT)
  FROM PS_ADDRESSES D
 WHERE D.EMPLID=A.EMPLID
   AND  (D.ADDRESS_TYPE = 'HOME' OR  D.ADDRESS_TYPE = 'MAIL')
   AND D.EFF_STATUS='A'
   AND D.EFFDT<= CURRENT DATE)

Lepa's picture
User offline. Last seen 2 years 20 weeks ago. Offline
Joined: 06/23/2008
Posts: 591
Re: Select most recent (as on current date) active address ...

The below will give you the 'MAIL' address type, play around with it and see if you can get it to work for you.

SELECT   a.emplid, a.effdt, MAX (a.address_type)
    FROM ps_addresses a
  WHERE a.eff_status = 'A'
    AND (a.address_type = 'HOME' OR a.address_type = 'MAIL')
    AND a.effdt =
           (SELECT MAX (effdt)
              FROM ps_addresses d
             WHERE d.emplid = a.emplid
               AND (d.address_type = 'HOME' OR d.address_type = 'MAIL')
               AND d.eff_status = 'A'
               AND d.effdt <= SYSDATE)
GROUP BY a.emplid, a.effdt;

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

simrose's picture
User offline. Last seen 6 years 32 weeks ago. Offline
Joined: 08/01/2008
Posts: 33
Re: Select most recent (as on current date) active address ...

Thanks Lepa for the update
Here is what is need , Can any one more fine tune this

SELECT DISTINCT A.EMPLID
 ,A.EFFDT
,A.ADDRESS_TYPE
,A.ADDRESS1
 , A.ADDRESS2
 , A.ADDRESS3
 , A.ADDRESS4
 , A.CITY
 , A.STATE
 , A.POSTAL
 ,A.COUNTRY
FROM PS_ADDRESSES A
 WHERE  A.EFF_STATUS='A'
   AND  A.ADDRESS_TYPE = (SELECT  MAX (a.address_type)
FROM ps_addresses a
WHERE a.eff_status = 'A'
AND (a.address_type = 'HOME' OR a.address_type = 'POA')
AND a.effdt =
(SELECT MAX (effdt)
FROM ps_addresses d
WHERE d.emplid = a.emplid
AND (d.address_type = 'HOME' OR d.address_type = 'POA')
AND d.eff_status = 'A'
AND d.effdt <= CURRENT DATE))
   AND A.EFFDT= (
 SELECT MAX(EFFDT)
  FROM PS_ADDRESSES D
 WHERE D.EMPLID=A.EMPLID
   AND  (D.ADDRESS_TYPE = A.ADDRESS_TYPE  )
   AND D.EFF_STATUS='A'
   AND D.EFFDT<= CURRENT DATE)