UFN_WPBIOGRAPHICAL_DATAREFRESHCHANGEHISTORY
Returns a table containing change history from refresh for Biographical.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @WEALTHID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN |
Definition
Copy
CREATE function [dbo].[UFN_WPBIOGRAPHICAL_DATAREFRESHCHANGEHISTORY](
@WEALTHID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
returns table
as
return
(
with WPBIOGRAPHICAL_CTE as
(
select
WPBIOGRAPHICAL.ID,
WPBIOGRAPHICAL.WEALTHID
from
WPBIOGRAPHICAL
where
(WPBIOGRAPHICAL.WEALTHID = @WEALTHID or @WEALTHID is null)
), OLDVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATEADDED,
KEYNAME,
--Insert Data Columns Here
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL
)as(
select
Row_Number() over (partition by WPBIOGRAPHICAL.ID order by WPBIOGRAPHICALHISTORY.DATEADDED) as REVISIONNUMBER,
WPBIOGRAPHICAL.WEALTHID,
WPBIOGRAPHICALHISTORY.PARTIALHASH,
WPBIOGRAPHICALHISTORY.SOURCE,
WPBIOGRAPHICALHISTORY.DATEADDED,
--Set Key Name Field Here
WPBIOGRAPHICALHISTORY.FULLNAME as KEYNAME,
--Insert Data Columns Here
cast(WPBIOGRAPHICALHISTORY.FULLNAME as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.LINE1 as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.CITY as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.STATE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.ZIP as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.A2LINE1 as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.A2CITY as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.A2STATE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.A2ZIP as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.GENDER as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.BIRTHDATE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.BIRTHPLACE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.DECEASEDDATE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.OCCUPATION as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.EDUCATION as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.FAMILY as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.POSITIONSHELD as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.CERTIFICATIONS as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.CAREER as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.AWARDS as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.MEMBERSHIPS as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.RESEARCHINTERESTS as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.POLITICALRELIGIOUSAFFILIATIONS as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.CIVICMILITARYSERVICE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.THOUGHTSONLIFE as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.LAW as nvarchar(4000)),
cast(WPBIOGRAPHICALHISTORY.PERSONAL as nvarchar(4000))
from
dbo.WPBIOGRAPHICALHISTORY
inner join WPBIOGRAPHICAL_CTE as WPBIOGRAPHICAL on
WPBIOGRAPHICAL.ID = WPBIOGRAPHICALHISTORY.WPBIOGRAPHICALID
),
NEWVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATE,
KEYNAME,
--Insert Data Columns Here
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL
)as(
select
(OLDVERSIONS.REVISIONNUMBER - 1) as REVISIONNUMBER,
OLDVERSIONS.WEALTHID,
OLDVERSIONS.PARTIALHASH,
OLDVERSIONS.SOURCE,
OLDVERSIONS.DATEADDED as DATE,
--Set Key Name Field Here
OLDVERSIONS.KEYNAME,
--Insert Data Columns Here
OLDVERSIONS.FULLNAME,
OLDVERSIONS.LINE1,
OLDVERSIONS.CITY,
OLDVERSIONS.STATE,
OLDVERSIONS.ZIP,
OLDVERSIONS.A2LINE1,
OLDVERSIONS.A2CITY,
OLDVERSIONS.A2STATE,
OLDVERSIONS.A2ZIP,
OLDVERSIONS.GENDER,
OLDVERSIONS.BIRTHDATE,
OLDVERSIONS.BIRTHPLACE,
OLDVERSIONS.DECEASEDDATE,
OLDVERSIONS.OCCUPATION,
OLDVERSIONS.EDUCATION,
OLDVERSIONS.FAMILY,
OLDVERSIONS.POSITIONSHELD,
OLDVERSIONS.CERTIFICATIONS,
OLDVERSIONS.CAREER,
OLDVERSIONS.AWARDS,
OLDVERSIONS.MEMBERSHIPS,
OLDVERSIONS.RESEARCHINTERESTS,
OLDVERSIONS.POLITICALRELIGIOUSAFFILIATIONS,
OLDVERSIONS.CIVICMILITARYSERVICE,
OLDVERSIONS.THOUGHTSONLIFE,
OLDVERSIONS.LAW,
OLDVERSIONS.PERSONAL
from
OLDVERSIONS
where
(@STARTDATE is null or OLDVERSIONS.DATEADDED >= @STARTDATE) and
(@ENDDATE is null or OLDVERSIONS.DATEADDED <= @ENDDATE)
),
REVISIONS(
WEALTHID,
PARTIALHASH,
SOURCE,
DATE,
KEYNAME,
--Insert Data Columns Here (Including "_NEW" Columns)
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL,
FULLNAME_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
A2LINE1_NEW,
A2CITY_NEW,
A2STATE_NEW,
A2ZIP_NEW,
GENDER_NEW,
BIRTHDATE_NEW,
BIRTHPLACE_NEW,
DECEASEDDATE_NEW,
OCCUPATION_NEW,
EDUCATION_NEW,
FAMILY_NEW,
POSITIONSHELD_NEW,
CERTIFICATIONS_NEW,
CAREER_NEW,
AWARDS_NEW,
MEMBERSHIPS_NEW,
RESEARCHINTERESTS_NEW,
POLITICALRELIGIOUSAFFILIATIONS_NEW,
CIVICMILITARYSERVICE_NEW,
THOUGHTSONLIFE_NEW,
LAW_NEW,
PERSONAL_NEW
)as(
select
OLDVERSIONS.WEALTHID,
OLDVERSIONS.PARTIALHASH,
NEWVERSIONS.SOURCE,
NEWVERSIONS.DATE,
NEWVERSIONS.KEYNAME,
--Insert Data Columns Here (Including "_NEW" Columns)
OLDVERSIONS.FULLNAME as FULLNAME,
OLDVERSIONS.LINE1 as LINE1,
OLDVERSIONS.CITY as CITY,
OLDVERSIONS.STATE as STATE,
OLDVERSIONS.ZIP as ZIP,
OLDVERSIONS.A2LINE1 as A2LINE1,
OLDVERSIONS.A2CITY as A2CITY,
OLDVERSIONS.A2STATE as A2STATE,
OLDVERSIONS.A2ZIP as A2ZIP,
OLDVERSIONS.GENDER as GENDER,
OLDVERSIONS.BIRTHDATE as BIRTHDATE,
OLDVERSIONS.BIRTHPLACE as BIRTHPLACE,
OLDVERSIONS.DECEASEDDATE as DECEASEDDATE,
OLDVERSIONS.OCCUPATION as OCCUPATION,
OLDVERSIONS.EDUCATION as EDUCATION,
OLDVERSIONS.FAMILY as FAMILY,
OLDVERSIONS.POSITIONSHELD as POSITIONSHELD,
OLDVERSIONS.CERTIFICATIONS as CERTIFICATIONS,
OLDVERSIONS.CAREER as CAREER,
OLDVERSIONS.AWARDS as AWARDS,
OLDVERSIONS.MEMBERSHIPS as MEMBERSHIPS,
OLDVERSIONS.RESEARCHINTERESTS as RESEARCHINTERESTS,
OLDVERSIONS.POLITICALRELIGIOUSAFFILIATIONS as POLITICALRELIGIOUSAFFILIATIONS,
OLDVERSIONS.CIVICMILITARYSERVICE as CIVICMILITARYSERVICE,
OLDVERSIONS.THOUGHTSONLIFE as THOUGHTSONLIFE,
OLDVERSIONS.LAW as LAW,
OLDVERSIONS.PERSONAL as PERSONAL,
NEWVERSIONS.FULLNAME as FULLNAME_NEW,
NEWVERSIONS.LINE1 as LINE1_NEW,
NEWVERSIONS.CITY as CITY_NEW,
NEWVERSIONS.STATE as STATE_NEW,
NEWVERSIONS.ZIP as ZIP_NEW,
NEWVERSIONS.A2LINE1 as A2LINE1_NEW,
NEWVERSIONS.A2CITY as A2CITY_NEW,
NEWVERSIONS.A2STATE as A2STATE_NEW,
NEWVERSIONS.A2ZIP as A2ZIP_NEW,
NEWVERSIONS.GENDER as GENDER_NEW,
NEWVERSIONS.BIRTHDATE as BIRTHDATE_NEW,
NEWVERSIONS.BIRTHPLACE as BIRTHPLACE_NEW,
NEWVERSIONS.DECEASEDDATE as DECEASEDDATE_NEW,
NEWVERSIONS.OCCUPATION as OCCUPATION_NEW,
NEWVERSIONS.EDUCATION as EDUCATION_NEW,
NEWVERSIONS.FAMILY as FAMILY_NEW,
NEWVERSIONS.POSITIONSHELD as POSITIONSHELD_NEW,
NEWVERSIONS.CERTIFICATIONS as CERTIFICATIONS_NEW,
NEWVERSIONS.CAREER as CAREER_NEW,
NEWVERSIONS.AWARDS as AWARDS_NEW,
NEWVERSIONS.MEMBERSHIPS as MEMBERSHIPS_NEW,
NEWVERSIONS.RESEARCHINTERESTS as RESEARCHINTERESTS_NEW,
NEWVERSIONS.POLITICALRELIGIOUSAFFILIATIONS as POLITICALRELIGIOUSAFFILIATIONS_NEW,
NEWVERSIONS.CIVICMILITARYSERVICE as CIVICMILITARYSERVICE_NEW,
NEWVERSIONS.THOUGHTSONLIFE as THOUGHTSONLIFE_NEW,
NEWVERSIONS.LAW as LAW_NEW,
NEWVERSIONS.PERSONAL as PERSONAL_NEW
from
OLDVERSIONS
inner join NEWVERSIONS on
OLDVERSIONS.REVISIONNUMBER = NEWVERSIONS.REVISIONNUMBER and
OLDVERSIONS.WEALTHID = NEWVERSIONS.WEALTHID and
OLDVERSIONS.PARTIALHASH = NEWVERSIONS.PARTIALHASH
)
select
CONSTITUENT.ID as CONSTITUENTID,
CONSTITUENT.NAME,
CHANGES.SOURCE,
CHANGES.KEYNAME,
CHANGES.DATE,
WEALTHPOINTDATAREFRESHNOTIFICATIONFIELD.FIELDTRANSLATION,
CHANGES.OLDVALUE,
CHANGES.NEWVALUE
from(
select
WEALTHID,
PARTIALHASH,
SOURCE,
KEYNAME,
DATE,
FIELDNAME,
OLDVALUE,
NEWVALUE
from(
select
WEALTHID,
PARTIALHASH,
SOURCE,
KEYNAME,
DATE,
--Insert Data Columns Here (Including "_NEW" Columns)
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL,
FULLNAME_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
A2LINE1_NEW,
A2CITY_NEW,
A2STATE_NEW,
A2ZIP_NEW,
GENDER_NEW,
BIRTHDATE_NEW,
BIRTHPLACE_NEW,
DECEASEDDATE_NEW,
OCCUPATION_NEW,
EDUCATION_NEW,
FAMILY_NEW,
POSITIONSHELD_NEW,
CERTIFICATIONS_NEW,
CAREER_NEW,
AWARDS_NEW,
MEMBERSHIPS_NEW,
RESEARCHINTERESTS_NEW,
POLITICALRELIGIOUSAFFILIATIONS_NEW,
CIVICMILITARYSERVICE_NEW,
THOUGHTSONLIFE_NEW,
LAW_NEW,
PERSONAL_NEW
from
REVISIONS
)as SOURCEDATA
unpivot(
OLDVALUE for FIELDNAME IN(
--Insert Data Columns Here (Excluding "_NEW" Columns)
FULLNAME,
LINE1,
CITY,
STATE,
ZIP,
A2LINE1,
A2CITY,
A2STATE,
A2ZIP,
GENDER,
BIRTHDATE,
BIRTHPLACE,
DECEASEDDATE,
OCCUPATION,
EDUCATION,
FAMILY,
POSITIONSHELD,
CERTIFICATIONS,
CAREER,
AWARDS,
MEMBERSHIPS,
RESEARCHINTERESTS,
POLITICALRELIGIOUSAFFILIATIONS,
CIVICMILITARYSERVICE,
THOUGHTSONLIFE,
LAW,
PERSONAL
))as UNPIVOTEDDATA1
unpivot(
NEWVALUE for FIELDNAME_NEW IN(
--Insert Data Columns Here (Only "_NEW" Columns)
FULLNAME_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
A2LINE1_NEW,
A2CITY_NEW,
A2STATE_NEW,
A2ZIP_NEW,
GENDER_NEW,
BIRTHDATE_NEW,
BIRTHPLACE_NEW,
DECEASEDDATE_NEW,
OCCUPATION_NEW,
EDUCATION_NEW,
FAMILY_NEW,
POSITIONSHELD_NEW,
CERTIFICATIONS_NEW,
CAREER_NEW,
AWARDS_NEW,
MEMBERSHIPS_NEW,
RESEARCHINTERESTS_NEW,
POLITICALRELIGIOUSAFFILIATIONS_NEW,
CIVICMILITARYSERVICE_NEW,
THOUGHTSONLIFE_NEW,
LAW_NEW,
PERSONAL_NEW
)) as UNPIVOTEDDATA2
where
FIELDNAME_NEW = FIELDNAME + '_NEW'and
OLDVALUE <> NEWVALUE and
NEWVALUE is not null and
NEWVALUE <> ''
) as CHANGES
left join dbo.CONSTITUENT on
CONSTITUENT.ID = CHANGES.WEALTHID
left join dbo.WEALTHPOINTDATAREFRESHNOTIFICATIONFIELD on
CHANGES.FIELDNAME = WEALTHPOINTDATAREFRESHNOTIFICATIONFIELD.FIELDNAME and
WEALTHPOINTDATAREFRESHNOTIFICATIONFIELD.TABLENAME = 'WPBIOGRAPHICAL'
)