VIEWID

(
PIN,
NAME,
NUMORGID,
EMPLOYMENTSTARTDATE,
EMPLOYMENTENDDATE,
DateModified,
FIRSTNAME,
prvFIRSTNAME,
MIDDLENAME,
prvMIDDLENAME,
LASTNAME,
prvLASTNAME,
SUFFIXNAME,
prvSUFFIXNAME,
INITIALS,
prvINITIALS,
TITLENAME,
prvTITLENAME,
CALLINGNAME,
prvCALLINGNAME,
NATIONALITY,
prvNATIONALITY,
PLACEOFBIRTH,
prvPLACEOFBIRTH,
BIRTHDATE,
prvBIRTHDATE,
COUNTRYOFBIRTH,
prvCOUNTRYOFBIRTH,
Picture,
TELEPHONE,
prvTELEPHONE,
HOMEAIRPORT,
prvHOMEAIRPORT,
HOMEAIRPORT2,
prvHOMEAIRPORT2,
MARITALSTATUS,
prvMARITALSTATUS,
TRAVELTIME,
prvTRAVELTIME,
TRAVELTIME2,
prvTRAVELTIME2,
BLOODGROUP,
prvBLOODGROUP,
ALLERGY,
prvALLERGY,
ADDRESS_COUNTRY,
prvADDRESS_COUNTRY,
ADDRESS1,
prvADDRESS1,
ADDRESS2,
prvADDRESS2,
ADDRESS3,
prvADDRESS3,
POSTCODE,
prvPOSTCODE,
POSTPLACE,
prvPOSTPLACE,
AADDRESS_COUNTRY,
prvAADDRESS_COUNTRY,
AADDRESS1,
prvAADDRESS1,
AADDRESS2,
prvAADDRESS2,
AADDRESS3,
prvAADDRESS3,
APOSTCODE,
prvAPOSTCODE,
APOSTPLACE,
prvAPOSTPLACE
)
AS
WITH cteAuditsP01 AS
(
	SELECT                
	PIN,
	FIRSTNAME,
	MIDDLENAME,
	LASTNAME,
	SUFFIXNAME,
	PICTURENO,
	TELEPHONE,
	INITIALS,
	TITLENAME,
	CALLINGNAME,
	NATIONALITY,
	PLACEOFBIRTH,
	BIRTHDATE,
	COUNTRYOFBIRTH,
	HOMEAIRPORT,
	HOMEAIRPORT2,
	MARITALSTATUS,
	TRAVELTIME,
	TRAVELTIME2,
	BLOODGROUP,
	ALLERGY,
	--FIRST ADDRESS 
	ADDRESS_COUNTRY,
	ADDRESS1,
	ADDRESS2,
	ADDRESS3,
	POSTCODE,
	POSTPLACE,
	--SECOND ADDRESS
	AADDRESS_COUNTRY,
	AADDRESS1,
	AADDRESS2,
	AADDRESS3,
	APOSTCODE,
	APOSTPLACE,
	REPL_MODIFIEDDATE,
	repl_ModifiedBySite,
	UPDATEDBY,
	CREATEDBY,
	CREATETIME,
	AUDIT_LINENR,
	UPDATEDTIME,
	SEQUENCENO,
	CLIENT                NUMORGID,
	EMPLOYMENTSTARTDATE,
	EMPLOYMENTENDDATE     FROM audit_pw001p01 WHERE DBACTION IN ('UPDATE')
),
 cteHistoryp01
AS (
       SELECT cur.PIN,
              CUR.TELEPHONE,
              PRV.TELEPHONE          AS prvTELEPHONE,
              cur.FIRSTNAME,
              PRV.FIRSTNAME 'prvFirstname',
              cur.MIDDLENAME,
              prv.MIDDLENAME 'prvMiddleName',
              cur.LASTNAME,
              prv.LASTNAME 'prvLastname',
              cur.SUFFIXNAME,
              prv.SUFFIXNAME 'prvSuffixname',
              cur.INITIALS,
              prv.INITIALS 'prvInitials',
              cur.TITLENAME,
              prv.TITLENAME 'prvTitlename',
              cur.CALLINGNAME,
              prv.CALLINGNAME 'prvCallingname',
              curCountry.COUNTRYNAME NATIONALITY,
              prvCountry.COUNTRYNAME 'prvNationality',
              cur.PLACEOFBIRTH,
              prv.PLACEOFBIRTH 'prvPlaceofBirth',
              CONVERT(VARCHAR(12), CONVERT(DATE, cur.BIRTHDATE), 107) BIRTHDATE,
              CONVERT(VARCHAR(12), CONVERT(DATE, prv.BIRTHDATE), 107) 'prvBirthdate',
              cur.COUNTRYOFBIRTH,
              prv.COUNTRYOFBIRTH        prvCountryOfBirth,
              cur.HOMEAIRPORT,
              prv.HOMEAIRPORT           prvHomeAirport,
              cur.HOMEAIRPORT2,
              prv.HOMEAIRPORT2          prvHomeAirport2,
              cur.MARITALSTATUS,
              prv.MARITALSTATUS         prvMaritalStatus,
              cur.TRAVELTIME,
              prv.TRAVELTIME            prvTravelTime,
              cur.TRAVELTIME2,
              prv.TRAVELTIME2           prvTravelTime2,
              cur.BLOODGROUP,
              prv.BLOODGROUP            prvBloodGroup,
              cur.ALLERGY,
              prv.ALLERGY               prvAllergy,
              cur.ADDRESS_COUNTRY,
              prv.ADDRESS_COUNTRY       prvAddress_Country,
              cur.ADDRESS1,
              prv.ADDRESS1              prvAddress1,
              cur.ADDRESS2,
              prv.ADDRESS2              prvAddress2,
              cur.ADDRESS3,
              prv.Address3              prvAddress3,
              cur.POSTCODE,
              prv.POSTCODE              prvPostCode,
              cur.POSTPLACE,
              prv.POSTPLACE             prvPostPlace,
              cur.AADDRESS_COUNTRY,
              prv.AADDRESS_COUNTRY      prvAADDRESS_COUNTRY,
              cur.AADDRESS1,
              prv.AADDRESS1             prvAADDRESS1,
              cur.AADDRESS2,
              prv.AADDRESS2             prvAADDRESS2,
              cur.AADDRESS3,
              prv.AADDRESS3             prvAADDRESS3,
              cur.APOSTCODE,
              prv.APOSTCODE             prvAPostcode,
              cur.APOSTPLACE,
              prv.APOSTPLACE            prvAPOSTPLACE,
              cur.SEQUENCENO,
              cur.REPL_MODIFIEDDATE     curChangeDate,
              prv.REPL_MODIFIEDDATE     prvChangeDate,
              cur.createtime,
              cur.UPDATEDBY,
              prv.UPDATEDBY             prvUpdatedby,
              cur.UPDATEDTIME,
              cur.CREATEDBY,
              cur.audit_linenr,
              CUR.NUMORGID,
              CUR.EMPLOYMENTENDDATE,
              CUR.EMPLOYMENTSTARTDATE,
              CUR.PICTURENO,
              PRV.PICTURENO          AS prvPictureNo,
              CASE 
                   WHEN CUR.PICTURENO <> PRV.PICTURENO
                        OR CUR.PICTURENO IS NULL THEN 'Updated'
                      ELSE ''
                 END                 AS Picture
          FROM   cteAuditsP01 cur
                 LEFT JOIN cteAuditsP01 prv
                      ON  prv.SEQUENCENO = cur.SEQUENCENO
                      AND prv.REPL_MODIFIEDDATE < cur.REPL_MODIFIEDDATE
                      AND NOT EXISTS 
                          (
                              SELECT 1
                              FROM   cteAuditsP01 t
                              WHERE  t.SEQUENCENO = prv.SEQUENCENO
                                     AND t.REPL_MODIFIEDDATE < cur.REPL_MODIFIEDDATE
                                     AND t.REPL_MODIFIEDDATE > prv.REPL_MODIFIEDDATE
                          )
                   LEFT JOIN PWCOUNTRY curCountry ON cur.NATIONALITY = curCountry.COUNTRYCODE
                   LEFT JOIN PWCOUNTRY prvCountry ON prv.NATIONALITY = prvCountry.COUNTRYCODE
   )
                                                    
SELECT TOP(99.9999) PERCENT
       p01.PIN,
       p01.NAME,
       P01.CLIENT NUMORGID,
       P01.EMPLOYMENTSTARTDATE,
       P01.EMPLOYMENTENDDATE,
       p01hist.curChangeDate    AS DateModified,
       CASE 
            WHEN p01hist.FIRSTNAME = p01hist.prvFIRSTNAME THEN ''
            ELSE p01hist.FIRSTNAME
       END                      AS FIRSTNAME,
       CASE 
            WHEN p01hist.FIRSTNAME = p01hist.prvFIRSTNAME THEN ''
            ELSE p01hist.prvFIRSTNAME
       END                      AS prvFIRSTNAME,
       CASE 
            WHEN p01hist.MIDDLENAME = p01hist.prvMIDDLENAME THEN ''
            ELSE P01HIST.MIDDLENAME
       END                      AS MIDDLENAME,
       CASE 
            WHEN p01hist.MIDDLENAME = p01hist.prvMIDDLENAME THEN ''
            ELSE P01HIST.prvMIDDLENAME
       END                      AS prvMIDDLENAME,
       CASE 
            WHEN p01hist.LASTNAME = p01hist.prvLASTNAME THEN ''
            ELSE P01HIST.LASTNAME
       END                      AS LASTNAME,
       CASE 
            WHEN p01hist.LASTNAME = p01hist.prvLASTNAME THEN ''
            ELSE P01HIST.prvLASTNAME
       END                      AS prvLASTNAME,
       CASE 
            WHEN p01hist.SUFFIXNAME = p01hist.prvSUFFIXNAME THEN ''
            ELSE P01HIST.SUFFIXNAME
       END                      AS SUFFIXNAME,
       CASE 
            WHEN p01hist.SUFFIXNAME = p01hist.prvSUFFIXNAME THEN ''
            ELSE P01HIST.prvSUFFIXNAME
       END                      AS prvSUFFIXNAME,
       CASE 
            WHEN p01hist.INITIALS = p01hist.prvINITIALS THEN ''
            ELSE P01HIST.INITIALS
       END                      AS INITIALS,
       CASE 
            WHEN p01hist.INITIALS = p01hist.prvINITIALS THEN ''
            ELSE P01HIST.prvINITIALS
       END                      AS prvINITIALS,
       CASE 
            WHEN p01hist.TITLENAME = p01hist.prvTITLENAME THEN ''
            ELSE P01HIST.TITLENAME
       END                      AS TITLENAME,
       CASE 
            WHEN p01hist.TITLENAME = p01hist.prvTITLENAME THEN ''
            ELSE P01HIST.prvTITLENAME
       END                      AS prvTITLENAME,
       CASE 
            WHEN p01hist.CALLINGNAME = p01hist.prvCALLINGNAME THEN ''
            ELSE P01HIST.CALLINGNAME
       END                      AS CALLINGNAME,
       CASE 
            WHEN p01hist.CALLINGNAME = p01hist.prvCALLINGNAME THEN ''
            ELSE P01HIST.prvCALLINGNAME
       END                      AS prvCALLINGNAME,
       CASE 
            WHEN p01hist.NATIONALITY = p01hist.prvNATIONALITY THEN ''
            ELSE P01HIST.NATIONALITY
       END                      AS NATIONALITY,
       CASE 
            WHEN p01hist.NATIONALITY = p01hist.prvNATIONALITY THEN ''
            ELSE P01HIST.prvNATIONALITY
       END                      AS prvNATIONALITY,
       CASE 
            WHEN p01hist.PLACEOFBIRTH = p01hist.prvPLACEOFBIRTH THEN ''
            ELSE P01HIST.PLACEOFBIRTH
       END                      AS PLACEOFBIRTH,
       CASE 
            WHEN p01hist.PLACEOFBIRTH = p01hist.prvPLACEOFBIRTH THEN ''
            ELSE P01HIST.prvPLACEOFBIRTH
       END                      AS prvPLACEOFBIRTH,
       CASE 
            WHEN p01hist.BIRTHDATe = p01hist.prvBIRTHDATE THEN ''
            ELSE p01hist.BIRTHDATE
       END                      AS BIRTHDATE,
       CASE 
            WHEN p01hist.BIRTHDATe = p01hist.prvBIRTHDATE THEN ''
            ELSE p01hist.prvBIRTHDATE
       END                      AS prvBIRTHDATE,
       CASE 
            WHEN p01hist.COUNTRYOFBIRTH = p01hist.prvCOUNTRYOFBIRTH THEN ''
            ELSE P01HIST.COUNTRYOFBIRTH
       END                      AS COUNTRYOFBIRTH,
       CASE 
            WHEN p01hist.COUNTRYOFBIRTH = p01hist.prvCOUNTRYOFBIRTH THEN ''
            ELSE P01HIST.prvCOUNTRYOFBIRTH
       END                      AS prvCOUNTRYOFBIRTH,
       PICTURE,
       CASE 
            WHEN p01hist.Telephone = P01HIST.PRVTELEPHONE THEN ''
            ELSE P01HIST.TELEPHONE
       END                      AS TELEPHONE,
       CASE 
            WHEN p01hist.Telephone = P01HIST.PRVTELEPHONE THEN ''
            ELSE P01HIST.prvTELEPHONE
       END                      AS prvTELEPHONE,
       CASE 
            WHEN p01hist.HOMEAIRPORT = p01hist.prvHOMEAIRPORT THEN ''
            ELSE p01hist.HOMEAIRPORT
       END                      AS HOMEAIRPORT,
       CASE 
            WHEN p01hist.HOMEAIRPORT = p01hist.prvHOMEAIRPORT THEN ''
            ELSE p01hist.prvHomeAirport
       END                      AS prvHOMEAIRPORT,
       CASE 
            WHEN p01hist.HOMEAIRPORT2 = p01hist.prvHOMEAIRPORT2 THEN ''
            ELSE p01hist.HOMEAIRPORT2
       END                      AS HOMEAIRPORT2,
       CASE 
            WHEN p01hist.HOMEAIRPORT2 = p01hist.prvHOMEAIRPORT2 THEN ''
            ELSE p01hist.prvHOMEAIRPORT2
       END                      AS prvHOMEAIRPORT2,
       CASE 
            WHEN p01hist.MARITALSTATUS = p01hist.prvMARITALSTATUS THEN ''
            ELSE P01HIST.MARITALSTATUS
       END                      AS MARITALSTATUS,
       CASE 
            WHEN p01hist.MARITALSTATUS = p01hist.prvMARITALSTATUS THEN ''
            ELSE P01HIST.prvMaritalStatus
       END                      AS prvMARITALSTATUS,
       CASE 
            WHEN p01hist.TRAVELTIME = p01hist.prvTRAVELTIME THEN ''
            ELSE p01hist.TRAVELTIME
       END                      AS TRAVELTIME,
       CASE 
            WHEN p01hist.TRAVELTIME = p01hist.prvTRAVELTIME THEN ''
            ELSE p01hist.prvTRAVELTIME
       END                      AS prvTRAVELTIME,
       CASE 
            WHEN p01hist.TRAVELTIME2 = p01hist.prvTRAVELTIME2 THEN ''
            ELSE P01HIST.TRAVELTIME2
       END                      AS TRAVELTIME2,
       CASE 
            WHEN p01hist.TRAVELTIME = p01hist.prvTRAVELTIME THEN ''
            ELSE p01hist.prvTravelTime2
       END                      AS prvTRAVELTIME2,
       CASE 
            WHEN p01hist.BLOODGROUP = p01hist.prvBLOODGROUP THEN ''
            ELSE P01HIST.BLOODGROUP
       END                      AS BLOODGROUP,
       CASE 
            WHEN p01hist.BLOODGROUP = p01hist.prvBLOODGROUP THEN ''
            ELSE P01HIST.prvBLOODGROUP
       END                      AS prvBLOODGROUP,
       CASE 
            WHEN p01hist.ALLERGY = p01hist.prvALLERGY THEN ''
            ELSE P01HIST.ALLERGY
       END                      AS ALLERGY,
       CASE 
            WHEN p01hist.ALLERGY = p01hist.prvALLERGY THEN ''
            ELSE P01HIST.prvALLERGY
       END                      AS prvALLERGY,
       CASE 
            WHEN p01hist.ADDRESS_COUNTRY = p01hist.prvADDRESS_COUNTRY THEN ''
            ELSE p01hist.ADDRESS_COUNTRY
       END                      AS ADDRESS_COUNTRY,
       CASE 
            WHEN p01hist.ADDRESS_COUNTRY = p01hist.prvADDRESS_COUNTRY THEN ''
            ELSE p01hist.prvADDRESS_COUNTRY
       END                      AS prvADDRESS_COUNTRY,
       CASE 
            WHEN p01hist.ADDRESS1 = p01hist.prvADDRESS1 THEN ''
            ELSE P01HIST.ADDRESS1
       END                      AS ADDRESS1,
       CASE 
            WHEN p01hist.ADDRESS1 = p01hist.prvADDRESS1 THEN ''
            ELSE P01HIST.prvADDRESS1
       END                      AS prvADDRESS1,
       CASE 
            WHEN p01hist.ADDRESS2 = p01hist.prvADDRESS2 THEN ''
            ELSE p01hist.ADDRESS2
       END                      AS ADDRESS2,
       CASE 
            WHEN p01hist.ADDRESS1 = p01hist.prvADDRESS1 THEN ''
            ELSE P01HIST.prvADDRESS2
       END                      AS prvADDRESS2,
       CASE 
            WHEN p01hist.ADDRESS3 = p01hist.prvADDRESS3 THEN ''
            ELSE p01hist.ADDRESS3
       END                      AS ADDRESS3,
       CASE 
            WHEN p01hist.ADDRESS3 = p01hist.prvADDRESS3 THEN ''
            ELSE p01hist.prvADDRESS3
       END                      AS prvADDRESS3,
       CASE 
            WHEN p01hist.POSTCODE = p01hist.prvPOSTCODE THEN ''
            ELSE P01HIST.POSTCODE
       END                      AS POSTCODE,
       CASE 
            WHEN p01hist.POSTCODE = p01hist.prvPOSTCODE THEN ''
            ELSE P01HIST.prvPOSTCODE
       END                      AS prvPOSTCODE,
       CASE 
            WHEN p01hist.POSTPLACE = p01hist.prvPOSTPLACE THEN ''
            ELSE p01hist.POSTPLACE
       END                      AS POSTPLACE,
       CASE 
            WHEN p01hist.POSTPLACE = p01hist.prvPOSTPLACE THEN ''
            ELSE p01hist.prvPOSTPLACE
       END                      AS prvPOSTPLACE,
       CASE 
            WHEN p01hist.AADDRESS_COUNTRY = p01hist.prvAADDRESS_COUNTRY THEN ''
            ELSE P01HIST.AADDRESS_COUNTRY
       END                      AS AADDRESS_COUNTRY,
       CASE 
            WHEN p01hist.AADDRESS_COUNTRY = p01hist.prvAADDRESS_COUNTRY THEN ''
            ELSE P01HIST.prvAADDRESS_COUNTRY
       END                      AS prvAADDRESS_COUNTRY,
       CASE 
            WHEN p01hist.AADDRESS1 = p01hist.prvAADDRESS1 THEN ''
            ELSE p01hist.AADDRESS1
       END                      AS AADDRESS1,
       CASE 
            WHEN p01hist.AADDRESS1 = p01hist.prvAADDRESS1 THEN ''
            ELSE p01hist.prvAADDRESS1
       END                      AS prvAADDRESS1,
       CASE 
            WHEN p01hist.AADDRESS2 = p01hist.prvAADDRESS2 THEN ''
            ELSE P01HIST.AADDRESS2
       END                      AS AADDRESS2,
       CASE 
            WHEN p01hist.AADDRESS2 = p01hist.prvAADDRESS2 THEN ''
            ELSE P01HIST.prvAADDRESS2
       END                      AS prvAADDRESS2,
       CASE 
            WHEN p01hist.AADDRESS3 = p01hist.prvAADDRESS3 THEN ''
            ELSE P01HIST.AADDRESS3
       END                      AS AADDRESS3,
       CASE 
            WHEN p01hist.AADDRESS3 = p01hist.prvAADDRESS3 THEN ''
            ELSE P01HIST.prvAADDRESS3
       END                      AS prvAADDRESS3,
       CASE 
            WHEN p01hist.APOSTCODE = p01hist.prvAPOSTCODE THEN ''
            ELSE p01hist.APOSTCODE
       END                      AS APOSTCODE,
       CASE 
            WHEN p01hist.APOSTCODE = p01hist.prvAPOSTCODE THEN ''
            ELSE p01hist.prvAPOSTCODE
       END                      AS prvAPOSTCODE,
       CASE 
            WHEN p01hist.APOSTPLACE = p01hist.prvAPOSTPLACE THEN ''
            ELSE P01HIST.APOSTPLACE
       END                      AS APOSTPLACE,
       CASE 
            WHEN p01hist.APOSTPLACE = p01hist.prvAPOSTPLACE THEN ''
            ELSE P01HIST.prvAPOSTPLACE
       END AS prvAPOSTPLACE
FROM   pw001p01 p01
       LEFT JOIN cteHistoryp01 p01hist
            ON  p01hist.SEQUENCENO = p01.SEQUENCENO
WHERE  P01HIST.UPDATEDBY = 'APP'
       AND P01HIST.curChangeDate >= DATEADD(DD, -30, GETDATE())
       AND (
               p01hist.FIRSTNAME <> p01hist.prvFirstname
               OR p01hist.MIDDLENAME <> p01hist.prvMiddleName
               OR p01hist.LASTNAME <> p01hist.prvLastname
               OR p01hist.SUFFIXNAME <> p01hist.prvSuffixname
               OR p01hist.INITIALS <> p01hist.prvInitials
               OR p01hist.TITLENAME <> p01hist.prvTitlename
               OR p01hist.CALLINGNAME <> p01hist.prvCallingname
               OR p01hist.NATIONALITY <> p01hist.prvNationality
               OR p01hist.PLACEOFBIRTH <> p01hist.prvPlaceofBirth
               OR p01hist.BIRTHDATE <> p01hist.prvBirthdate
               OR p01hist.COUNTRYOFBIRTH <> p01hist.prvCountryOfBirth
               OR p01hist.HOMEAIRPORT <> p01hist.prvHomeAirport
               OR p01hist.HOMEAIRPORT2 <> p01hist.prvHomeAirport2
               OR p01hist.MARITALSTATUS <> p01hist.prvMaritalStatus
               OR p01hist.TRAVELTIME <> p01hist.prvTravelTime
               OR p01hist.TRAVELTIME2 <> p01hist.prvTravelTime2
               OR p01hist.BLOODGROUP <> p01hist.prvBloodGroup
               OR p01hist.ALLERGY <> p01hist.prvALLERGY
               OR p01hist.ADDRESS_COUNTRY <> p01hist.prvAddress_Country
               OR p01hist.ADDRESS1 <> p01hist.prvAddress1
               OR p01hist.ADDRESS2 <> p01hist.prvAddress2
               OR p01hist.ADDRESS3 <> p01hist.prvAddress3
               OR p01hist.POSTCODE <> p01hist.prvPostCode
               OR p01hist.POSTPLACE <> p01hist.prvPostPlace
               OR p01hist.AADDRESS_COUNTRY <> p01hist.prvAADDRESS_COUNTRY
               OR p01hist.AADDRESS1 <> p01hist.prvAADDRESS1
               OR p01hist.AADDRESS2 <> p01hist.prvAADDRESS2
               OR p01hist.AADDRESS3 <> p01hist.prvAADDRESS3
               OR p01hist.APOSTCODE <> p01hist.prvAPostcode
               OR p01hist.APOSTPLACE <> p01hist.prvAPOSTPLACE
               OR P01HIST.TELEPHONE <> p01hist.PRVTELEPHONE
               OR (p01hist.PICTURENO <> p01hist.prvPictureNo)
               OR (
                      p01hist.PICTURENO IS NULL
                      AND p01hist.prvPictureNo IS NOT NULL
                  )
               OR (
                      P01HIST.PICTURENO IS NOT NULL
                      AND P01HIST.prvPictureNo IS NULL
                  )
           )
ORDER BY
       p01hist.[curChangeDate]     DESC