UFN_WPBUSINESSOWNERSHIP_DATAREFRESHCHANGEHISTORY
Returns a table containing change history from Data Refresh for Business Ownership.
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_WPBUSINESSOWNERSHIP_DATAREFRESHCHANGEHISTORY](
@WEALTHID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
returns table
as
return
(
with WPBUSINESSOWNERSHIP_CTE as
(
select
WPBUSINESSOWNERSHIP.ID,
WPBUSINESSOWNERSHIP.WEALTHID
from
WPBUSINESSOWNERSHIP
where
(WPBUSINESSOWNERSHIP.WEALTHID = @WEALTHID or @WEALTHID is null)
), OLDVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATEADDED,
KEYNAME,
--Insert Data Columns Here
FULLNAME,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
HELD,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDER,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
WPBUSINESSOWNERSHIPBIOS,
WPBUSINESSOWNERSHIPOWNERSHIP
)as(
select
Row_Number() over (partition by WPBUSINESSOWNERSHIP.ID order by WPBUSINESSOWNERSHIPHISTORY.DATEADDED) as REVISIONNUMBER,
WPBUSINESSOWNERSHIP.WEALTHID,
WPBUSINESSOWNERSHIPHISTORY.PARTIALHASH,
WPBUSINESSOWNERSHIPHISTORY.SOURCE,
WPBUSINESSOWNERSHIPHISTORY.DATEADDED,
--Set Key Name Field Here
WPBUSINESSOWNERSHIPHISTORY.COMPANY as KEYNAME,
--Insert Data Columns Here
cast(WPBUSINESSOWNERSHIPHISTORY.FULLNAME as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.TITLE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.LINE1 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.CITY as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.STATE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.ZIP as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.COMPANY as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.PHONE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.LINE_OF_BUSINESS_DESCRIPTION as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SALES_VOLUME as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.YEAR_STARTED as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.EMPLOYEES_TOTAL as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.PERCENTAGE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.VALUATION as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SPOUSEIND as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.HELD as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.MAILINGLINE1 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.MAILINGCITY as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.MAILINGSTATE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.MAILINGZIP as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.HOMELINE1 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.HOMECITY as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.HOMESTATE as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.HOMEZIP as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SICNAME as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.YEAROFBIRTH as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.GENDER as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE1 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE2 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE3 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE4 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE5 as nvarchar(4000)),
cast(WPBUSINESSOWNERSHIPHISTORY.SIC_CODE6 as nvarchar(4000)),
cast(case when WPBUSINESSOWNERSHIPHISTORY.RATIO1 = 0 then null else WPBUSINESSOWNERSHIPHISTORY.RATIO1 end as nvarchar(4000)),
cast(case when WPBUSINESSOWNERSHIPHISTORY.RATIO2 = 0 then null else WPBUSINESSOWNERSHIPHISTORY.RATIO2 end as nvarchar(4000)),
cast(case when WPBUSINESSOWNERSHIPHISTORY.RATIO3 = 0 then null else WPBUSINESSOWNERSHIPHISTORY.RATIO3 end as nvarchar(4000)),
cast(case when WPBUSINESSOWNERSHIPHISTORY.RATIO4 = 0 then null else WPBUSINESSOWNERSHIPHISTORY.RATIO4 end as nvarchar(4000)),
cast(case when WPBUSINESSOWNERSHIPHISTORY.RATIO5 = 0 then null else WPBUSINESSOWNERSHIPHISTORY.RATIO5 end as nvarchar(4000)),
cast(case when WPBUSINESSOWNERSHIPHISTORY.RATIO6 = 0 then null else WPBUSINESSOWNERSHIPHISTORY.RATIO6 end as nvarchar(4000)),
cast((select NAME as "Name", BIO as "Bio" from dbo.WPBUSINESSOWNERSHIPBIOSHISTORY where WPBUSINESSOWNERSHIPHISTORYID = WPBUSINESSOWNERSHIPHISTORY.ID order by NAME asc for xml path('Biography')) as nvarchar(4000)),
cast((select NAME as "Name", PERCENTOWNERSHIP as "PctOwnership", VALUE as "OwnershipValue" from dbo.WPBUSINESSOWNERSHIPOWNERSHIPHISTORY where WPBUSINESSOWNERSHIPHISTORYID = WPBUSINESSOWNERSHIPHISTORY.ID for xml path('Ownership')) as nvarchar(4000))
from
dbo.WPBUSINESSOWNERSHIPHISTORY
inner join WPBUSINESSOWNERSHIP_CTE as WPBUSINESSOWNERSHIP on
WPBUSINESSOWNERSHIP.ID = WPBUSINESSOWNERSHIPHISTORY.WPBUSINESSOWNERSHIPID
),
NEWVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATE,
KEYNAME,
--Insert Data Columns Here
FULLNAME,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
HELD,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDER,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
WPBUSINESSOWNERSHIPBIOS,
WPBUSINESSOWNERSHIPOWNERSHIP
)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.TITLE,
OLDVERSIONS.LINE1,
OLDVERSIONS.CITY,
OLDVERSIONS.STATE,
OLDVERSIONS.ZIP,
OLDVERSIONS.COMPANY,
OLDVERSIONS.PHONE,
OLDVERSIONS.LINE_OF_BUSINESS_DESCRIPTION,
OLDVERSIONS.SALES_VOLUME,
OLDVERSIONS.YEAR_STARTED,
OLDVERSIONS.EMPLOYEES_TOTAL,
OLDVERSIONS.SIC_CODE,
OLDVERSIONS.PERCENTAGE,
OLDVERSIONS.VALUATION,
OLDVERSIONS.SPOUSEIND,
OLDVERSIONS.HELD,
OLDVERSIONS.MAILINGLINE1,
OLDVERSIONS.MAILINGCITY,
OLDVERSIONS.MAILINGSTATE,
OLDVERSIONS.MAILINGZIP,
OLDVERSIONS.HOMELINE1,
OLDVERSIONS.HOMECITY,
OLDVERSIONS.HOMESTATE,
OLDVERSIONS.HOMEZIP,
OLDVERSIONS.SICNAME,
OLDVERSIONS.YEAROFBIRTH,
OLDVERSIONS.GENDER,
OLDVERSIONS.SIC_CODE1,
OLDVERSIONS.SIC_CODE2,
OLDVERSIONS.SIC_CODE3,
OLDVERSIONS.SIC_CODE4,
OLDVERSIONS.SIC_CODE5,
OLDVERSIONS.SIC_CODE6,
OLDVERSIONS.RATIO1,
OLDVERSIONS.RATIO2,
OLDVERSIONS.RATIO3,
OLDVERSIONS.RATIO4,
OLDVERSIONS.RATIO5,
OLDVERSIONS.RATIO6,
OLDVERSIONS.WPBUSINESSOWNERSHIPBIOS,
OLDVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP
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,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
HELD,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDER,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
WPBUSINESSOWNERSHIPBIOS,
WPBUSINESSOWNERSHIPOWNERSHIP,
FULLNAME_NEW,
TITLE_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
COMPANY_NEW,
PHONE_NEW,
LINE_OF_BUSINESS_DESCRIPTION_NEW,
SALES_VOLUME_NEW,
YEAR_STARTED_NEW,
EMPLOYEES_TOTAL_NEW,
SIC_CODE_NEW,
PERCENTAGE_NEW,
VALUATION_NEW,
SPOUSEIND_NEW,
HELD_NEW,
MAILINGLINE1_NEW,
MAILINGCITY_NEW,
MAILINGSTATE_NEW,
MAILINGZIP_NEW,
HOMELINE1_NEW,
HOMECITY_NEW,
HOMESTATE_NEW,
HOMEZIP_NEW,
SICNAME_NEW,
YEAROFBIRTH_NEW,
GENDER_NEW,
SIC_CODE1_NEW,
SIC_CODE2_NEW,
SIC_CODE3_NEW,
SIC_CODE4_NEW,
SIC_CODE5_NEW,
SIC_CODE6_NEW,
RATIO1_NEW,
RATIO2_NEW,
RATIO3_NEW,
RATIO4_NEW,
RATIO5_NEW,
RATIO6_NEW,
WPBUSINESSOWNERSHIPBIOS_NEW,
WPBUSINESSOWNERSHIPOWNERSHIP_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.TITLE as TITLE,
OLDVERSIONS.LINE1 as LINE1,
OLDVERSIONS.CITY as CITY,
OLDVERSIONS.STATE as STATE,
OLDVERSIONS.ZIP as ZIP,
OLDVERSIONS.COMPANY as COMPANY,
OLDVERSIONS.PHONE as PHONE,
OLDVERSIONS.LINE_OF_BUSINESS_DESCRIPTION as LINE_OF_BUSINESS_DESCRIPTION,
OLDVERSIONS.SALES_VOLUME as SALES_VOLUME,
OLDVERSIONS.YEAR_STARTED as YEAR_STARTED,
OLDVERSIONS.EMPLOYEES_TOTAL as EMPLOYEES_TOTAL,
OLDVERSIONS.SIC_CODE as SIC_CODE,
OLDVERSIONS.PERCENTAGE as PERCENTAGE,
OLDVERSIONS.VALUATION as VALUATION,
OLDVERSIONS.SPOUSEIND as SPOUSEIND,
OLDVERSIONS.HELD as HELD,
OLDVERSIONS.MAILINGLINE1 as MAILINGLINE1,
OLDVERSIONS.MAILINGCITY as MAILINGCITY,
OLDVERSIONS.MAILINGSTATE as MAILINGSTATE,
OLDVERSIONS.MAILINGZIP as MAILINGZIP,
OLDVERSIONS.HOMELINE1 as HOMELINE1,
OLDVERSIONS.HOMECITY as HOMECITY,
OLDVERSIONS.HOMESTATE as HOMESTATE,
OLDVERSIONS.HOMEZIP as HOMEZIP,
OLDVERSIONS.SICNAME as SICNAME,
OLDVERSIONS.YEAROFBIRTH as YEAROFBIRTH,
OLDVERSIONS.GENDER as GENDER,
OLDVERSIONS.SIC_CODE1 as SIC_CODE1,
OLDVERSIONS.SIC_CODE2 as SIC_CODE2,
OLDVERSIONS.SIC_CODE3 as SIC_CODE3,
OLDVERSIONS.SIC_CODE4 as SIC_CODE4,
OLDVERSIONS.SIC_CODE5 as SIC_CODE5,
OLDVERSIONS.SIC_CODE6 as SIC_CODE6,
OLDVERSIONS.RATIO1 as RATIO1,
OLDVERSIONS.RATIO2 as RATIO2,
OLDVERSIONS.RATIO3 as RATIO3,
OLDVERSIONS.RATIO4 as RATIO4,
OLDVERSIONS.RATIO5 as RATIO5,
OLDVERSIONS.RATIO6 as RATIO6,
case
when OLDVERSIONS.WPBUSINESSOWNERSHIPBIOS = NEWVERSIONS.WPBUSINESSOWNERSHIPBIOS or NEWVERSIONS.WPBUSINESSOWNERSHIPBIOS is null then cast('' as nvarchar(4000))
else cast('Values have changed' as nvarchar(4000))
end as WPBUSINESSOWNERSHIPBIOS,
case
when OLDVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP = NEWVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP or NEWVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP is null then cast('' as nvarchar(4000))
else cast('Values have changed' as nvarchar(4000))
end as WPBUSINESSOWNERSHIPOWNERSHIP,
NEWVERSIONS.FULLNAME as FULLNAME_NEW,
NEWVERSIONS.TITLE as TITLE_NEW,
NEWVERSIONS.LINE1 as LINE1_NEW,
NEWVERSIONS.CITY as CITY_NEW,
NEWVERSIONS.STATE as STATE_NEW,
NEWVERSIONS.ZIP as ZIP_NEW,
NEWVERSIONS.COMPANY as COMPANY_NEW,
NEWVERSIONS.PHONE as PHONE_NEW,
NEWVERSIONS.LINE_OF_BUSINESS_DESCRIPTION as LINE_OF_BUSINESS_DESCRIPTION_NEW,
NEWVERSIONS.SALES_VOLUME as SALES_VOLUME_NEW,
NEWVERSIONS.YEAR_STARTED as YEAR_STARTED_NEW,
NEWVERSIONS.EMPLOYEES_TOTAL as EMPLOYEES_TOTAL_NEW,
NEWVERSIONS.SIC_CODE as SIC_CODE_NEW,
NEWVERSIONS.PERCENTAGE as PERCENTAGE_NEW,
NEWVERSIONS.VALUATION as VALUATION_NEW,
NEWVERSIONS.SPOUSEIND as SPOUSEIND_NEW,
NEWVERSIONS.HELD as HELD_NEW,
NEWVERSIONS.MAILINGLINE1 as MAILINGLINE1_NEW,
NEWVERSIONS.MAILINGCITY as MAILINGCITY_NEW,
NEWVERSIONS.MAILINGSTATE as MAILINGSTATE_NEW,
NEWVERSIONS.MAILINGZIP as MAILINGZIP_NEW,
NEWVERSIONS.HOMELINE1 as HOMELINE1_NEW,
NEWVERSIONS.HOMECITY as HOMECITY_NEW,
NEWVERSIONS.HOMESTATE as HOMESTATE_NEW,
NEWVERSIONS.HOMEZIP as HOMEZIP_NEW,
NEWVERSIONS.SICNAME as SICNAME_NEW,
NEWVERSIONS.YEAROFBIRTH as YEAROFBIRTH_NEW,
NEWVERSIONS.GENDER as GENDER_NEW,
NEWVERSIONS.SIC_CODE1 as SIC_CODE1_NEW,
NEWVERSIONS.SIC_CODE2 as SIC_CODE2_NEW,
NEWVERSIONS.SIC_CODE3 as SIC_CODE3_NEW,
NEWVERSIONS.SIC_CODE4 as SIC_CODE4_NEW,
NEWVERSIONS.SIC_CODE5 as SIC_CODE5_NEW,
NEWVERSIONS.SIC_CODE6 as SIC_CODE6_NEW,
NEWVERSIONS.RATIO1 as RATIO1_NEW,
NEWVERSIONS.RATIO2 as RATIO2_NEW,
NEWVERSIONS.RATIO3 as RATIO3_NEW,
NEWVERSIONS.RATIO4 as RATIO4_NEW,
NEWVERSIONS.RATIO5 as RATIO5_NEW,
NEWVERSIONS.RATIO6 as RATIO6_NEW,
case
when NEWVERSIONS.WPBUSINESSOWNERSHIPBIOS = OLDVERSIONS.WPBUSINESSOWNERSHIPBIOS or NEWVERSIONS.WPBUSINESSOWNERSHIPBIOS is null then cast('' as nvarchar(4000))
else cast('Go to constituent to review updates' as nvarchar(4000))
end as WPBUSINESSOWNERSHIPBIOS_NEW,
case
when NEWVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP = OLDVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP or NEWVERSIONS.WPBUSINESSOWNERSHIPOWNERSHIP is null then cast('' as nvarchar(4000))
else cast('Go to constituent to review updates' as nvarchar(4000))
end as WPBUSINESSOWNERSHIPOWNERSHIP_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,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
HELD,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDER,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
WPBUSINESSOWNERSHIPBIOS,
WPBUSINESSOWNERSHIPOWNERSHIP,
FULLNAME_NEW,
TITLE_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
COMPANY_NEW,
PHONE_NEW,
LINE_OF_BUSINESS_DESCRIPTION_NEW,
SALES_VOLUME_NEW,
YEAR_STARTED_NEW,
EMPLOYEES_TOTAL_NEW,
SIC_CODE_NEW,
PERCENTAGE_NEW,
VALUATION_NEW,
SPOUSEIND_NEW,
HELD_NEW,
MAILINGLINE1_NEW,
MAILINGCITY_NEW,
MAILINGSTATE_NEW,
MAILINGZIP_NEW,
HOMELINE1_NEW,
HOMECITY_NEW,
HOMESTATE_NEW,
HOMEZIP_NEW,
SICNAME_NEW,
YEAROFBIRTH_NEW,
GENDER_NEW,
SIC_CODE1_NEW,
SIC_CODE2_NEW,
SIC_CODE3_NEW,
SIC_CODE4_NEW,
SIC_CODE5_NEW,
SIC_CODE6_NEW,
RATIO1_NEW,
RATIO2_NEW,
RATIO3_NEW,
RATIO4_NEW,
RATIO5_NEW,
RATIO6_NEW,
WPBUSINESSOWNERSHIPBIOS_NEW,
WPBUSINESSOWNERSHIPOWNERSHIP_NEW
from
REVISIONS
)as SOURCEDATA
unpivot(
OLDVALUE for FIELDNAME IN(
--Insert Data Columns Here (Excluding "_NEW" Columns)
FULLNAME,
TITLE,
LINE1,
CITY,
STATE,
ZIP,
COMPANY,
PHONE,
LINE_OF_BUSINESS_DESCRIPTION,
SALES_VOLUME,
YEAR_STARTED,
EMPLOYEES_TOTAL,
SIC_CODE,
PERCENTAGE,
VALUATION,
SPOUSEIND,
HELD,
MAILINGLINE1,
MAILINGCITY,
MAILINGSTATE,
MAILINGZIP,
HOMELINE1,
HOMECITY,
HOMESTATE,
HOMEZIP,
SICNAME,
YEAROFBIRTH,
GENDER,
SIC_CODE1,
SIC_CODE2,
SIC_CODE3,
SIC_CODE4,
SIC_CODE5,
SIC_CODE6,
RATIO1,
RATIO2,
RATIO3,
RATIO4,
RATIO5,
RATIO6,
WPBUSINESSOWNERSHIPBIOS,
WPBUSINESSOWNERSHIPOWNERSHIP
))as UNPIVOTEDDATA1
unpivot(
NEWVALUE for FIELDNAME_NEW IN(
--Insert Data Columns Here (Only "_NEW" Columns)
FULLNAME_NEW,
TITLE_NEW,
LINE1_NEW,
CITY_NEW,
STATE_NEW,
ZIP_NEW,
COMPANY_NEW,
PHONE_NEW,
LINE_OF_BUSINESS_DESCRIPTION_NEW,
SALES_VOLUME_NEW,
YEAR_STARTED_NEW,
EMPLOYEES_TOTAL_NEW,
SIC_CODE_NEW,
PERCENTAGE_NEW,
VALUATION_NEW,
SPOUSEIND_NEW,
HELD_NEW,
MAILINGLINE1_NEW,
MAILINGCITY_NEW,
MAILINGSTATE_NEW,
MAILINGZIP_NEW,
HOMELINE1_NEW,
HOMECITY_NEW,
HOMESTATE_NEW,
HOMEZIP_NEW,
SICNAME_NEW,
YEAROFBIRTH_NEW,
GENDER_NEW,
SIC_CODE1_NEW,
SIC_CODE2_NEW,
SIC_CODE3_NEW,
SIC_CODE4_NEW,
SIC_CODE5_NEW,
SIC_CODE6_NEW,
RATIO1_NEW,
RATIO2_NEW,
RATIO3_NEW,
RATIO4_NEW,
RATIO5_NEW,
RATIO6_NEW,
WPBUSINESSOWNERSHIPBIOS_NEW,
WPBUSINESSOWNERSHIPOWNERSHIP_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 = 'WPBUSINESSOWNERSHIP'
)