help me!!!

[Andres95]

¿La secuencia se reinicia cada dia?
¿Estas usando SQL Server 2000?

--------------------------------------------

Ok, suponiendo que se reinicia, solo faltaria agrupar el resultado y aplicarle un maximo a la secuencia de B quedando como sigue:

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  JOB_EFFDT
      ,Max(B.EFFSEQ) EFFSEQ
      ,B.HOURLY_RT
      ,B.CURRENCY_CD
      ,B.PAYGROUP
      ,B.COMPANY
      ,B.GL_PAY_TYPE
      ,B.[ACTION]
      ,B.ACTION_REASON
      ,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  TaXES_EFFDT
      ,D.SWT_MAR_STATUS
      --------------------
      ,E.EFFDT Address_Effdt
      ,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
Left 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
      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.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
Group by  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.HOURLY_RT
         ,B.CURRENCY_CD
         ,B.PAYGROUP
         ,B.COMPANY
         ,B.GL_PAY_TYPE
         ,B.[ACTION]
         ,B.ACTION_REASON
         ,B.ANNUAL_RT
         ,B.DEPTID
         ,B.FLSA_STATUS
         --------------------
         --------------------
         ,D.EFFDT
         ,D.SWT_MAR_STATUS
         --------------------
         ,E.EFFDT
         ,E.ADDRESS1
         ,E.ADDRESS2
         ,E.CITY
         ,E.COUNTRY
         ,E.POSTAL
         --------------------

Seria bueno revizar que los indices correctos esten presentes en las tablas que se estan consultando y que los Ids sean numericos, siendo asi no debes tener problemas con el tiempo de ejecución.

Saludos!