CREATE VIEW dbo.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
|