UFN_WPNONPROFITAFFILIATION_DATAREFRESHCHANGEHISTORY
Returns a table containing change history from Data Refresh for Nonprofit Affiliations.
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_WPNONPROFITAFFILIATION_DATAREFRESHCHANGEHISTORY](
@WEALTHID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
returns table
as
return
(
with WPNONPROFITAFFILIATION_CTE as
(
select
WPNONPROFITAFFILIATION.ID,
WPNONPROFITAFFILIATION.WEALTHID
from
WPNONPROFITAFFILIATION
where
(WPNONPROFITAFFILIATION.WEALTHID = @WEALTHID or @WEALTHID is null)
), OLDVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATEADDED,
KEYNAME,
--Insert Data Columns Here
TITLE,
SALARY,
DN_ORGANIZATION,
LINE1,
CITY,
STATE,
ZIP,
REVENUE,
PHONE,
FORMYEAR,
FILEDDATE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
LOCATION,
HCITY,
HSTATE,
HZIP
)as(
select
Row_Number() over (partition by WPNONPROFITAFFILIATION.ID order by WPNONPROFITAFFILIATIONHISTORY.DATEADDED) as REVISIONNUMBER,
WPNONPROFITAFFILIATION.WEALTHID,
WPNONPROFITAFFILIATIONHISTORY.PARTIALHASH,
WPNONPROFITAFFILIATIONHISTORY.SOURCE,
WPNONPROFITAFFILIATIONHISTORY.DATEADDED,
--Set Key Name Field Here
WPNONPROFITAFFILIATIONHISTORY.DN_ORGANIZATION as KEYNAME,
--Insert Data Columns Here
cast(WPNONPROFITAFFILIATIONHISTORY.TITLE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.SALARY as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.DN_ORGANIZATION as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.LINE1 as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.CITY as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.STATE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.ZIP as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.REVENUE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.PHONE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.FORMYEAR as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.FILEDDATE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.DESCRIPTION as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.TOTALASSETS as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.WEBSITE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.ORGFORMYEAR as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.RULINGYEAR as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.LOCATION as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.HCITY as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.HSTATE as nvarchar(4000)),
cast(WPNONPROFITAFFILIATIONHISTORY.HZIP as nvarchar(4000))
from
dbo.WPNONPROFITAFFILIATIONHISTORY
inner join WPNONPROFITAFFILIATION_CTE as WPNONPROFITAFFILIATION on
WPNONPROFITAFFILIATION.ID = WPNONPROFITAFFILIATIONHISTORY.WPNONPROFITAFFILIATIONID
),
NEWVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATE,
KEYNAME,
--Insert Data Columns Here
TITLE,
SALARY,
DN_ORGANIZATION,
LINE1,
CITY,
STATE,
ZIP,
REVENUE,
PHONE,
FORMYEAR,
FILEDDATE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
LOCATION,
HCITY,
HSTATE,
HZIP
)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.TITLE,
OLDVERSIONS.SALARY,
OLDVERSIONS.DN_ORGANIZATION,
OLDVERSIONS.LINE1,
OLDVERSIONS.CITY,
OLDVERSIONS.STATE,
OLDVERSIONS.ZIP,
OLDVERSIONS.REVENUE,
OLDVERSIONS.PHONE,
OLDVERSIONS.FORMYEAR,
OLDVERSIONS.FILEDDATE,
OLDVERSIONS.DESCRIPTION,
OLDVERSIONS.TOTALASSETS,
OLDVERSIONS.WEBSITE,
OLDVERSIONS.ORGFORMYEAR,
OLDVERSIONS.RULINGYEAR,
OLDVERSIONS.LOCATION,
OLDVERSIONS.HCITY,
OLDVERSIONS.HSTATE,
OLDVERSIONS.HZIP
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)
TITLE,
SALARY,
DN_ORGANIZATION,
LINE1,
CITY,
STATE,
ZIP,
REVENUE,
PHONE,
FORMYEAR,
FILEDDATE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
LOCATION,
HCITY,
HSTATE,
HZIP,
TITLE_NEW,
SALARY_NEW,
DN_ORGANIZATION_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
REVENUE_NEW,
PHONE_NEW,
FORMYEAR_NEW,
FILEDDATE_NEW,
DESCRIPTION_NEW,
TOTALASSETS_NEW,
WEBSITE_NEW,
ORGFORMYEAR_NEW,
RULINGYEAR_NEW,
LOCATION_NEW,
HCITY_NEW,
HSTATE_NEW,
HZIP_NEW
)as(
select
OLDVERSIONS.WEALTHID,
OLDVERSIONS.PARTIALHASH,
NEWVERSIONS.SOURCE,
NEWVERSIONS.DATE,
NEWVERSIONS.KEYNAME,
--Insert Data Columns Here (Including "_NEW" Columns)
OLDVERSIONS.TITLE as TITLE,
OLDVERSIONS.SALARY as SALARY,
OLDVERSIONS.DN_ORGANIZATION as DN_ORGANIZATION,
OLDVERSIONS.LINE1 as LINE1,
OLDVERSIONS.CITY as CITY,
OLDVERSIONS.STATE as STATE,
OLDVERSIONS.ZIP as ZIP,
OLDVERSIONS.REVENUE as REVENUE,
OLDVERSIONS.PHONE as PHONE,
OLDVERSIONS.FORMYEAR as FORMYEAR,
OLDVERSIONS.FILEDDATE as FILEDDATE,
OLDVERSIONS.DESCRIPTION as DESCRIPTION,
OLDVERSIONS.TOTALASSETS as TOTALASSETS,
OLDVERSIONS.WEBSITE as WEBSITE,
OLDVERSIONS.ORGFORMYEAR as ORGFORMYEAR,
OLDVERSIONS.RULINGYEAR as RULINGYEAR,
OLDVERSIONS.LOCATION as LOCATION,
OLDVERSIONS.HCITY as HCITY,
OLDVERSIONS.HSTATE as HSTATE,
OLDVERSIONS.HZIP as HZIP,
NEWVERSIONS.TITLE as TITLE_NEW,
NEWVERSIONS.SALARY as SALARY_NEW,
NEWVERSIONS.DN_ORGANIZATION as DN_ORGANIZATION_NEW,
NEWVERSIONS.LINE1 as LINE1_NEW,
NEWVERSIONS.CITY as CITY_NEW,
NEWVERSIONS.STATE as STATE_NEW,
NEWVERSIONS.ZIP as ZIP_NEW,
NEWVERSIONS.REVENUE as REVENUE_NEW,
NEWVERSIONS.PHONE as PHONE_NEW,
NEWVERSIONS.FORMYEAR as FORMYEAR_NEW,
NEWVERSIONS.FILEDDATE as FILEDDATE_NEW,
NEWVERSIONS.DESCRIPTION as DESCRIPTION_NEW,
NEWVERSIONS.TOTALASSETS as TOTALASSETS_NEW,
NEWVERSIONS.WEBSITE as WEBSITE_NEW,
NEWVERSIONS.ORGFORMYEAR as ORGFORMYEAR_NEW,
NEWVERSIONS.RULINGYEAR as RULINGYEAR_NEW,
NEWVERSIONS.LOCATION as LOCATION_NEW,
NEWVERSIONS.HCITY as HCITY_NEW,
NEWVERSIONS.HSTATE as HSTATE_NEW,
NEWVERSIONS.HZIP as HZIP_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)
TITLE,
SALARY,
DN_ORGANIZATION,
LINE1,
CITY,
STATE,
ZIP,
REVENUE,
PHONE,
FORMYEAR,
FILEDDATE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
LOCATION,
HCITY,
HSTATE,
HZIP,
TITLE_NEW,
SALARY_NEW,
DN_ORGANIZATION_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
REVENUE_NEW,
PHONE_NEW,
FORMYEAR_NEW,
FILEDDATE_NEW,
DESCRIPTION_NEW,
TOTALASSETS_NEW,
WEBSITE_NEW,
ORGFORMYEAR_NEW,
RULINGYEAR_NEW,
LOCATION_NEW,
HCITY_NEW,
HSTATE_NEW,
HZIP_NEW
from
REVISIONS
)as SOURCEDATA
unpivot(
OLDVALUE for FIELDNAME IN(
--Insert Data Columns Here (Excluding "_NEW" Columns)
TITLE,
SALARY,
DN_ORGANIZATION,
LINE1,
CITY,
STATE,
ZIP,
REVENUE,
PHONE,
FORMYEAR,
FILEDDATE,
DESCRIPTION,
TOTALASSETS,
WEBSITE,
ORGFORMYEAR,
RULINGYEAR,
LOCATION,
HCITY,
HSTATE,
HZIP
))as UNPIVOTEDDATA1
unpivot(
NEWVALUE for FIELDNAME_NEW IN(
--Insert Data Columns Here (Only "_NEW" Columns)
TITLE_NEW,
SALARY_NEW,
DN_ORGANIZATION_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
REVENUE_NEW,
PHONE_NEW,
FORMYEAR_NEW,
FILEDDATE_NEW,
DESCRIPTION_NEW,
TOTALASSETS_NEW,
WEBSITE_NEW,
ORGFORMYEAR_NEW,
RULINGYEAR_NEW,
LOCATION_NEW,
HCITY_NEW,
HSTATE_NEW,
HZIP_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 = 'WPNONPROFITAFFILIATION'
)