help me!!!

[Andres95]

- Se incluyo el EffDt y EffSeq para las tablas que pusiste entre parentesis, excepto una que no se menciona en el listado de campos inicial (PS_JOB_EARNS_DIST) pero de querer agregarla seria de forma similar al resto.

Espero te sirva para empezar...

Saludos!

Código:

Select A.EMPLID
      ,A.LAST_NAME
      ,A.SECOND_LAST_NAME
      ,A.FIRST_NAME
      ,A.MIDDLE_NAME
      ,A.BIRTHDATE
      ,A.MAR_STATUS
      ,A.SEX
      --------------------
      ,B.EFFDT
      ,B.EffSeq
      ,B.HOURLY_RT
      ,B.CURRENCY_CD
      ,B.PAYGROUP
      ,B.COMPANY
      ,B.GL_PAY_TYPE
      ,B.[ACTION]
      ,B.ACTION_REASON
      ,B.EFFDT
      ,B.ANNUAL_RT
      ,B.DEPTID
      ,B.FLSA_STATUS
      --------------------
      ,isnull((Select Top 1 NATIONAL_ID_TYPE From PS_PERS_NID  Where  EMPLID = A.EMPLID),'')  AS NATIONAL_ID_TYPE
      --------------------
      ,D.EFFDT
      ,D.EffSeq
      ,D.SWT_MAR_STATUS
      --------------------
      ,E.EFFDT
      ,E.EffSeq
      ,E.ADDRESS1
      ,E.ADDRESS2
      ,E.CITY
      ,E.COUNTRY
      ,E.POSTAL
      --------------------
      ,isnull((Select Top 1 PHONE From PS_EMERGENCY_CNTCT  Where  EMPLID = A.EMPLID),'')  AS PHONE
From  PS_PERSONAL_DATA A
Letf outer join
     (Select Jobs.EmplId
            ,Jobs.HOURLY_RT
            ,Jobs.CURRENCY_CD
            ,Jobs.PAYGROUP
            ,Jobs.COMPANY
            ,Jobs.GL_PAY_TYPE
            ,Jobs.ACTION
            ,Jobs.ACTION_REASON
            ,Jobs.EFFDT
            ,Jobs.EffSeq
            ,Jobs.ANNUAL_RT
            ,Jobs.DEPTID
            ,Jobs.FLSA_STATUS
     From    PS_JOB Jobs
     Inner join
           (SELECT EMPLID, MAX(EFFDT) EffDt FROM PS_JOB P Group By EMPLID) JobsMax
     On    Jobs.EmplId = JobsMax.EmplId  And
           Jobs.EffDt  = JobsMax.EffDt) B
On   A.EmplId = B.EmplId
Left outer join
     (Select  Taxes.EmplId
             ,Taxes.SWT_MAR_STATUS
             ,Taxes.EFFDT
             ,Taxes.EffSeq
      From    PS_STATE_TAX_DATA Taxes
      Inner join
              (SELECT EMPLID, MAX(EFFDT) EffDt FROM PS_STATE_TAX_DATA Group By EMPLID) TaxesMax
      On      Taxes.EmplId = TaxesMax.EmplId   And
              Taxes.EffDt  = TaxesMax.EffDt) D
On   A.EmplId = D.EmplId
Left outer join
     (Select Address.EmplId
            ,Address.EffDt
            ,Address.EffSeq
            ,Address.ADDRESS1
            ,Address.ADDRESS2
            ,Address.CITY
            ,Address.COUNTRY
            ,Address.POSTAL   
     From    PS_ADDRESSES Address
     Inner join
             (SELECT EMPLID, MAX(EFFDT)EffDt FROM PS_STATE_TAX_DATA Group By EMPLID) AddressMax
     On      Address.EmplId = AddressMax.EmplId   And
             Address.EffDt  = AddressMax.EffDt) E
On   A.EmplId = E.EmplId