UFN_WPPRIVATEFOUNDATION_DATAREFRESHCHANGEHISTORY
Returns a table containing change history from Data Refresh for Private Foundation.
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_WPPRIVATEFOUNDATION_DATAREFRESHCHANGEHISTORY](
@WEALTHID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
returns table
as
return
(
with WPPRIVATEFOUNDATION_CTE as
(
select
WPPRIVATEFOUNDATION.ID,
WPPRIVATEFOUNDATION.WEALTHID
from
WPPRIVATEFOUNDATION
where
(WPPRIVATEFOUNDATION.WEALTHID = @WEALTHID or @WEALTHID is null)
), OLDVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATEADDED,
KEYNAME,
--Insert Data Columns Here
TITLE,
RULING_YEAR,
COMPANY,
LINE1,
CITY,
STATE,
ZIP,
YEAR_ENDING,
PHONE,
DESCRIPTION,
TOTALASSETS_FMV,
COMP,
FORMYEAR,
FILEDDATE,
FAIRMARKETVALUEASSETS,
LOCATION,
REVENUE,
ORGFORMYEAR,
WEBSITE,
HCITY,
HSTATE,
HZIP
)as(
select
Row_Number() over (partition by WPPRIVATEFOUNDATION.ID order by WPPRIVATEFOUNDATIONHISTORY.DATEADDED) as REVISIONNUMBER,
WPPRIVATEFOUNDATION.WEALTHID,
WPPRIVATEFOUNDATIONHISTORY.PARTIALHASH,
WPPRIVATEFOUNDATIONHISTORY.SOURCE,
WPPRIVATEFOUNDATIONHISTORY.DATEADDED,
--Set Key Name Field Here
WPPRIVATEFOUNDATIONHISTORY.COMPANY as KEYNAME,
--Insert Data Columns Here
cast(WPPRIVATEFOUNDATIONHISTORY.TITLE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.RULING_YEAR as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.COMPANY as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.LINE1 as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.CITY as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.STATE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.ZIP as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.YEAR_ENDING as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.PHONE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.DESCRIPTION as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.TOTALASSETS_FMV as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.COMP as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.FORMYEAR as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.FILEDDATE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.FAIRMARKETVALUEASSETS as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.LOCATION as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.REVENUE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.ORGFORMYEAR as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.WEBSITE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.HCITY as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.HSTATE as nvarchar(4000)),
cast(WPPRIVATEFOUNDATIONHISTORY.HZIP as nvarchar(4000))
from
dbo.WPPRIVATEFOUNDATIONHISTORY
inner join WPPRIVATEFOUNDATION_CTE as WPPRIVATEFOUNDATION on
WPPRIVATEFOUNDATION.ID = WPPRIVATEFOUNDATIONHISTORY.WPPRIVATEFOUNDATIONID
),
NEWVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATE,
KEYNAME,
--Insert Data Columns Here
TITLE,
RULING_YEAR,
COMPANY,
LINE1,
CITY,
STATE,
ZIP,
YEAR_ENDING,
PHONE,
DESCRIPTION,
TOTALASSETS_FMV,
COMP,
FORMYEAR,
FILEDDATE,
FAIRMARKETVALUEASSETS,
LOCATION,
REVENUE,
ORGFORMYEAR,
WEBSITE,
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.RULING_YEAR,
OLDVERSIONS.COMPANY,
OLDVERSIONS.LINE1,
OLDVERSIONS.CITY,
OLDVERSIONS.STATE,
OLDVERSIONS.ZIP,
OLDVERSIONS.YEAR_ENDING,
OLDVERSIONS.PHONE,
OLDVERSIONS.DESCRIPTION,
OLDVERSIONS.TOTALASSETS_FMV,
OLDVERSIONS.COMP,
OLDVERSIONS.FORMYEAR,
OLDVERSIONS.FILEDDATE,
OLDVERSIONS.FAIRMARKETVALUEASSETS,
OLDVERSIONS.LOCATION,
OLDVERSIONS.REVENUE,
OLDVERSIONS.ORGFORMYEAR,
OLDVERSIONS.WEBSITE,
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,
RULING_YEAR,
COMPANY,
LINE1,
CITY,
STATE,
ZIP,
YEAR_ENDING,
PHONE,
DESCRIPTION,
TOTALASSETS_FMV,
COMP,
FORMYEAR,
FILEDDATE,
FAIRMARKETVALUEASSETS,
LOCATION,
REVENUE,
ORGFORMYEAR,
WEBSITE,
HCITY,
HSTATE,
HZIP,
TITLE_NEW,
RULING_YEAR_NEW,
COMPANY_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
YEAR_ENDING_NEW,
PHONE_NEW,
DESCRIPTION_NEW,
TOTALASSETS_FMV_NEW,
COMP_NEW,
FORMYEAR_NEW,
FILEDDATE_NEW,
FAIRMARKETVALUEASSETS_NEW,
LOCATION_NEW,
REVENUE_NEW,
ORGFORMYEAR_NEW,
WEBSITE_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.RULING_YEAR as RULING_YEAR,
OLDVERSIONS.COMPANY as COMPANY,
OLDVERSIONS.LINE1 as LINE1,
OLDVERSIONS.CITY as CITY,
OLDVERSIONS.STATE as STATE,
OLDVERSIONS.ZIP as ZIP,
OLDVERSIONS.YEAR_ENDING as YEAR_ENDING,
OLDVERSIONS.PHONE as PHONE,
OLDVERSIONS.DESCRIPTION as DESCRIPTION,
OLDVERSIONS.TOTALASSETS_FMV as TOTALASSETS_FMV,
OLDVERSIONS.COMP as COMP,
OLDVERSIONS.FORMYEAR as FORMYEAR,
OLDVERSIONS.FILEDDATE as FILEDDATE,
OLDVERSIONS.FAIRMARKETVALUEASSETS as FAIRMARKETVALUEASSETS,
OLDVERSIONS.LOCATION as LOCATION,
OLDVERSIONS.REVENUE as REVENUE,
OLDVERSIONS.ORGFORMYEAR as ORGFORMYEAR,
OLDVERSIONS.WEBSITE as WEBSITE,
OLDVERSIONS.HCITY as HCITY,
OLDVERSIONS.HSTATE as HSTATE,
OLDVERSIONS.HZIP as HZIP,
NEWVERSIONS.TITLE as TITLE_NEW,
NEWVERSIONS.RULING_YEAR as RULING_YEAR_NEW,
NEWVERSIONS.COMPANY as COMPANY_NEW,
NEWVERSIONS.LINE1 as LINE1_NEW,
NEWVERSIONS.CITY as CITY_NEW,
NEWVERSIONS.STATE as STATE_NEW,
NEWVERSIONS.ZIP as ZIP_NEW,
NEWVERSIONS.YEAR_ENDING as YEAR_ENDING_NEW,
NEWVERSIONS.PHONE as PHONE_NEW,
NEWVERSIONS.DESCRIPTION as DESCRIPTION_NEW,
NEWVERSIONS.TOTALASSETS_FMV as TOTALASSETS_FMV_NEW,
NEWVERSIONS.COMP as COMP_NEW,
NEWVERSIONS.FORMYEAR as FORMYEAR_NEW,
NEWVERSIONS.FILEDDATE as FILEDDATE_NEW,
NEWVERSIONS.FAIRMARKETVALUEASSETS as FAIRMARKETVALUEASSETS_NEW,
NEWVERSIONS.LOCATION as LOCATION_NEW,
NEWVERSIONS.REVENUE as REVENUE_NEW,
NEWVERSIONS.ORGFORMYEAR as ORGFORMYEAR_NEW,
NEWVERSIONS.WEBSITE as WEBSITE_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,
RULING_YEAR,
COMPANY,
LINE1,
CITY,
STATE,
ZIP,
YEAR_ENDING,
PHONE,
DESCRIPTION,
TOTALASSETS_FMV,
COMP,
FORMYEAR,
FILEDDATE,
FAIRMARKETVALUEASSETS,
LOCATION,
REVENUE,
ORGFORMYEAR,
WEBSITE,
HCITY,
HSTATE,
HZIP,
TITLE_NEW,
RULING_YEAR_NEW,
COMPANY_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
YEAR_ENDING_NEW,
PHONE_NEW,
DESCRIPTION_NEW,
TOTALASSETS_FMV_NEW,
COMP_NEW,
FORMYEAR_NEW,
FILEDDATE_NEW,
FAIRMARKETVALUEASSETS_NEW,
LOCATION_NEW,
REVENUE_NEW,
ORGFORMYEAR_NEW,
WEBSITE_NEW,
HCITY_NEW,
HSTATE_NEW,
HZIP_NEW
from
REVISIONS
)as SOURCEDATA
unpivot(
OLDVALUE for FIELDNAME IN(
--Insert Data Columns Here (Excluding "_NEW" Columns)
TITLE,
RULING_YEAR,
COMPANY,
LINE1,
CITY,
STATE,
ZIP,
YEAR_ENDING,
PHONE,
DESCRIPTION,
TOTALASSETS_FMV,
COMP,
FORMYEAR,
FILEDDATE,
FAIRMARKETVALUEASSETS,
LOCATION,
REVENUE,
ORGFORMYEAR,
WEBSITE,
HCITY,
HSTATE,
HZIP
))as UNPIVOTEDDATA1
unpivot(
NEWVALUE for FIELDNAME_NEW IN(
--Insert Data Columns Here (Only "_NEW" Columns)
TITLE_NEW,
RULING_YEAR_NEW,
COMPANY_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
YEAR_ENDING_NEW,
PHONE_NEW,
DESCRIPTION_NEW,
TOTALASSETS_FMV_NEW,
COMP_NEW,
FORMYEAR_NEW,
FILEDDATE_NEW,
FAIRMARKETVALUEASSETS_NEW,
LOCATION_NEW,
REVENUE_NEW,
ORGFORMYEAR_NEW,
WEBSITE_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 = 'WPPRIVATEFOUNDATION'
)