Ask Question
Help Others
Home › Forums › PeopleSoft › PeopleSoft Technical

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

  • Post reply
2 replies [Last post]
Mon, 02/14/2011 - 10:04pm
simrose's picture
simrose
User offline. Last seen 5 days 13 hours 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)

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
Top
Tue, 02/15/2011 - 2:49pm
#1
Lepa's picture
Lepa
User offline. Last seen 6 days 7 hours ago. Offline
Joined: 06/23/2008
Posts: 570
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

Top
  • reply
Wed, 02/16/2011 - 2:48am
#2
simrose's picture
simrose
User offline. Last seen 5 days 13 hours 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)

Top
  • reply

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.