UFN_WPSECURITIES_DATAREFRESHCHANGEHISTORY
Returns a table containing change history from Data Refresh for Securities.
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_WPSECURITIES_DATAREFRESHCHANGEHISTORY](
@WEALTHID uniqueidentifier = null,
@STARTDATE datetime = null,
@ENDDATE datetime = null
)
returns table
as
return
(
with WPSECURITIES_CTE as
(
select
WPSECURITIES.ID,
WPSECURITIES.WEALTHID
from
WPSECURITIES
where
(WPSECURITIES.WEALTHID = @WEALTHID or @WEALTHID is null)
), OLDVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATEADDED,
KEYNAME,
--Insert Data Columns Here
FULLNAME,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT
)as(
select
Row_Number() over (partition by WPSECURITIES.ID order by WPSECURITIESHISTORY.DATEADDED) as REVISIONNUMBER,
WPSECURITIES.WEALTHID,
WPSECURITIESHISTORY.PARTIALHASH,
WPSECURITIESHISTORY.SOURCE,
WPSECURITIESHISTORY.DATEADDED,
--Set Key Name Field Here
WPSECURITIESHISTORY.ISSUERNAME as KEYNAME,
--Insert Data Columns Here
cast(WPSECURITIESHISTORY.FULLNAME as nvarchar(4000)),
cast(WPSECURITIESHISTORY.CUSIP as nvarchar(4000)),
cast(WPSECURITIESHISTORY.FILERID as nvarchar(4000)),
cast(WPSECURITIESHISTORY.RELATIONSHIP as nvarchar(4000)),
cast(WPSECURITIESHISTORY.TICKER as nvarchar(4000)),
cast(WPSECURITIESHISTORY.ISSUERNAME as nvarchar(4000)),
cast(WPSECURITIESHISTORY.REPORTDATEDIRECT as nvarchar(4000)),
cast(WPSECURITIESHISTORY.REPORTDATEINDIRECT as nvarchar(4000)),
cast(WPSECURITIESHISTORY.AMOUNTOWNEDDIRECT as nvarchar(4000)),
cast(WPSECURITIESHISTORY.AMOUNTOWNEDINDIRECT as nvarchar(4000))
from
dbo.WPSECURITIESHISTORY
inner join WPSECURITIES_CTE as WPSECURITIES on
WPSECURITIES.ID = WPSECURITIESHISTORY.WPSECURITIESID
),
NEWVERSIONS(
REVISIONNUMBER,
WEALTHID,
PARTIALHASH,
SOURCE,
DATE,
KEYNAME,
--Insert Data Columns Here
FULLNAME,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT
)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.CUSIP,
OLDVERSIONS.FILERID,
OLDVERSIONS.RELATIONSHIP,
OLDVERSIONS.TICKER,
OLDVERSIONS.ISSUERNAME,
OLDVERSIONS.REPORTDATEDIRECT,
OLDVERSIONS.REPORTDATEINDIRECT,
OLDVERSIONS.AMOUNTOWNEDDIRECT,
OLDVERSIONS.AMOUNTOWNEDINDIRECT
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,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT,
FULLNAME_NEW,
CUSIP_NEW,
FILERID_NEW,
RELATIONSHIP_NEW,
TICKER_NEW,
ISSUERNAME_NEW,
REPORTDATEDIRECT_NEW,
REPORTDATEINDIRECT_NEW,
AMOUNTOWNEDDIRECT_NEW,
AMOUNTOWNEDINDIRECT_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.CUSIP as CUSIP,
OLDVERSIONS.FILERID as FILERID,
OLDVERSIONS.RELATIONSHIP as RELATIONSHIP,
OLDVERSIONS.TICKER as TICKER,
OLDVERSIONS.ISSUERNAME as ISSUERNAME,
OLDVERSIONS.REPORTDATEDIRECT as REPORTDATEDIRECT,
OLDVERSIONS.REPORTDATEINDIRECT as REPORTDATEINDIRECT,
OLDVERSIONS.AMOUNTOWNEDDIRECT as AMOUNTOWNEDDIRECT,
OLDVERSIONS.AMOUNTOWNEDINDIRECT as AMOUNTOWNEDINDIRECT,
NEWVERSIONS.FULLNAME as FULLNAME_NEW,
NEWVERSIONS.CUSIP as CUSIP_NEW,
NEWVERSIONS.FILERID as FILERID_NEW,
NEWVERSIONS.RELATIONSHIP as RELATIONSHIP_NEW,
NEWVERSIONS.TICKER as TICKER_NEW,
NEWVERSIONS.ISSUERNAME as ISSUERNAME_NEW,
NEWVERSIONS.REPORTDATEDIRECT as REPORTDATEDIRECT_NEW,
NEWVERSIONS.REPORTDATEINDIRECT as REPORTDATEINDIRECT_NEW,
NEWVERSIONS.AMOUNTOWNEDDIRECT as AMOUNTOWNEDDIRECT_NEW,
NEWVERSIONS.AMOUNTOWNEDINDIRECT as AMOUNTOWNEDINDIRECT_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,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT,
FULLNAME_NEW,
CUSIP_NEW,
FILERID_NEW,
RELATIONSHIP_NEW,
TICKER_NEW,
ISSUERNAME_NEW,
REPORTDATEDIRECT_NEW,
REPORTDATEINDIRECT_NEW,
AMOUNTOWNEDDIRECT_NEW,
AMOUNTOWNEDINDIRECT_NEW
from
REVISIONS
)as SOURCEDATA
unpivot(
OLDVALUE for FIELDNAME IN(
--Insert Data Columns Here (Excluding "_NEW" Columns)
FULLNAME,
CUSIP,
FILERID,
RELATIONSHIP,
TICKER,
ISSUERNAME,
REPORTDATEDIRECT,
REPORTDATEINDIRECT,
AMOUNTOWNEDDIRECT,
AMOUNTOWNEDINDIRECT
))as UNPIVOTEDDATA1
unpivot(
NEWVALUE for FIELDNAME_NEW IN(
--Insert Data Columns Here (Only "_NEW" Columns)
FULLNAME_NEW,
CUSIP_NEW,
FILERID_NEW,
RELATIONSHIP_NEW,
TICKER_NEW,
ISSUERNAME_NEW,
REPORTDATEDIRECT_NEW,
REPORTDATEINDIRECT_NEW,
AMOUNTOWNEDDIRECT_NEW,
AMOUNTOWNEDINDIRECT_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 = 'WPSECURITIES'
)