USP_GROUP_GETWEALTHSUMMARY
Returns a group's wealth summary.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GROUPID | uniqueidentifier | IN | |
| @REALESTATEASSETS | money | INOUT | |
| @SECURITIESASSETS | money | INOUT | |
| @BUSINESSASSETS | money | INOUT | |
| @AFFLUENCEINDICATORSASSETS | money | INOUT | |
| @OTHERASSETS | money | INOUT | |
| @TOTALASSETS | money | INOUT | |
| @INCOME | money | INOUT | |
| @SALARY | money | INOUT | |
| @COMPENSATION | money | INOUT | |
| @C_REALESTATEASSETS | money | INOUT | |
| @C_SECURITIESASSETS | money | INOUT | |
| @C_BUSINESSASSETS | money | INOUT | |
| @C_AFFLUENCEINDICATORSASSETS | money | INOUT | |
| @C_OTHERASSETS | money | INOUT | |
| @C_TOTALASSETS | money | INOUT | |
| @C_INCOME | money | INOUT | |
| @C_SALARY | money | INOUT | |
| @C_COMPENSATION | money | INOUT | |
| @NBIOGRAPHICAL | int | INOUT | |
| @NCAMPAIGN | int | INOUT | |
| @NFOUNDATION | int | INOUT | |
| @NGIFT | int | INOUT | |
| @NINCOME | int | INOUT | |
| @NAFFLUENCEINDICATORS | int | INOUT | |
| @NNONPROFIT | int | INOUT | |
| @NOTHERASSETS | int | INOUT | |
| @NBUSINESS | int | INOUT | |
| @NREALESTATE | int | INOUT | |
| @NSECURITIES | int | INOUT | |
| @C_NBIOGRAPHICAL | int | INOUT | |
| @C_NCAMPAIGN | int | INOUT | |
| @C_NFOUNDATION | int | INOUT | |
| @C_NGIFT | int | INOUT | |
| @C_NINCOME | int | INOUT | |
| @C_NAFFLUENCEINDICATORS | int | INOUT | |
| @C_NNONPROFIT | int | INOUT | |
| @C_NOTHERASSETS | int | INOUT | |
| @C_NBUSINESS | int | INOUT | |
| @C_NREALESTATE | int | INOUT | |
| @C_NSECURITIES | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_GROUP_GETWEALTHSUMMARY
(
@GROUPID uniqueidentifier,
@REALESTATEASSETS money = null output,
@SECURITIESASSETS money = null output,
@BUSINESSASSETS money = null output,
@AFFLUENCEINDICATORSASSETS money = null output,
@OTHERASSETS money = null output,
@TOTALASSETS money = null output,
@INCOME money = null output,
@SALARY money = null output,
@COMPENSATION money = null output,
@C_REALESTATEASSETS money = null output,
@C_SECURITIESASSETS money = null output,
@C_BUSINESSASSETS money = null output,
@C_AFFLUENCEINDICATORSASSETS money = null output,
@C_OTHERASSETS money = null output,
@C_TOTALASSETS money = null output,
@C_INCOME money = null output,
@C_SALARY money = null output,
@C_COMPENSATION money = null output,
@NBIOGRAPHICAL int = null output,
@NCAMPAIGN int = null output,
@NFOUNDATION int = null output,
@NGIFT int = null output,
@NINCOME int = null output,
@NAFFLUENCEINDICATORS int = null output,
@NNONPROFIT int = null output,
@NOTHERASSETS int = null output,
@NBUSINESS int = null output,
@NREALESTATE int = null output,
@NSECURITIES int = null output,
@C_NBIOGRAPHICAL int = null output,
@C_NCAMPAIGN int = null output,
@C_NFOUNDATION int = null output,
@C_NGIFT int = null output,
@C_NINCOME int = null output,
@C_NAFFLUENCEINDICATORS int = null output,
@C_NNONPROFIT int = null output,
@C_NOTHERASSETS int = null output,
@C_NBUSINESS int = null output,
@C_NREALESTATE int = null output,
@C_NSECURITIES int = null output
)
as
set nocount on
-- Store group members in a table variable
declare @CURRENTDATEEARLIESTTIME datetime;
set @CURRENTDATEEARLIESTTIME = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @GROUPMEMBERS table
(
MEMBERID uniqueidentifier
)
insert into @GROUPMEMBERS (MEMBERID)
select
ID
from dbo.UFN_GROUP_GETCURRENTMEMBERSNOPERMISSIONCHECK(@GROUPID);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from (
select
PARTIALHASH, DATECHANGED
from dbo.WPBIOGRAPHICAL
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
union all
select
PARTIALHASH, DATECHANGED
from dbo.WPBIOGRAPHICALDEMOGRAPHIC
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
) BIO
)
select
@NBIOGRAPHICAL = count(*)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPPOLITICALDONATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NCAMPAIGN = count(*)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by MATCHHASH order by DATECHANGED desc) as ROWNUMBER,
MATCHHASH
from dbo.WPPHILANTHROPICGIFT
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NGIFT = count(*)
from CTE
where
ROWNUMBER = 1 or
(MATCHHASH = '' or MATCHHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH,
SALARY
from dbo.WPNONPROFITAFFILIATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NNONPROFIT = count(*),
@SALARY = sum(SALARY)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH,
COMP
from dbo.WPPRIVATEFOUNDATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NFOUNDATION = count(*),
@COMPENSATION = sum(COMP)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from (
select
PARTIALHASH, DATECHANGED
from dbo.WPBIOGRAPHICAL
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
union all
select
PARTIALHASH, DATECHANGED
from dbo.WPBIOGRAPHICALDEMOGRAPHIC
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
) BIO
)
select
@C_NBIOGRAPHICAL = count(*)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPPOLITICALDONATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NCAMPAIGN = count(*)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPPHILANTHROPICGIFT
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NGIFT = count(*)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH,
SALARY
from dbo.WPNONPROFITAFFILIATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NNONPROFIT = count(*),
@C_SALARY = sum(SALARY)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH,
COMP
from dbo.WPPRIVATEFOUNDATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NFOUNDATION = count(*),
@C_COMPENSATION = sum(COMP)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
begin try;
with CTE as
(
select
OWNERSHIPVALUE,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPBUSINESSOWNERSHIP
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NBUSINESS = count(*),
@BUSINESSASSETS = sum(OWNERSHIPVALUE)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
RPASSETS,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPAFFLUENCEINDICATOR
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NAFFLUENCEINDICATORS = count(*),
@AFFLUENCEINDICATORSASSETS = sum(RPASSETS)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
select
@NOTHERASSETS =count(*),
@OTHERASSETS =sum(VALUE)
from dbo.WPOTHERASSET
where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and REJECTED=0;
with CTE as
(
select
TOTALCOMPENSATION,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPINCOMECOMPENSATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0 and
HISTORICCODE <> 1
)
select
@NINCOME = count(*),
@INCOME = sum(TOTALCOMPENSATION)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
PROPERTYVALUATION,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPREALESTATE
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0 and
HISTORICCODE <> 1
)
select
@NREALESTATE = count(*),
@REALESTATEASSETS = sum(PROPERTYVALUATION)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
VALUEDIRECT,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPSECURITIES
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
REJECTED=0
)
select
@NSECURITIES = count(*),
@SECURITIESASSETS = sum(VALUEDIRECT)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
OWNERSHIPVALUE,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPBUSINESSOWNERSHIP
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NBUSINESS = count(*),
@C_BUSINESSASSETS = sum(OWNERSHIPVALUE)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
RPASSETS,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPAFFLUENCEINDICATOR
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NAFFLUENCEINDICATORS = count(*),
@C_AFFLUENCEINDICATORSASSETS = sum(RPASSETS)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
select @C_NOTHERASSETS = count(*), @C_OTHERASSETS = sum(VALUE) from dbo.WPOTHERASSET where WEALTHID in (select MEMBERID from @GROUPMEMBERS) and CONFIRMED=1;
with CTE as
(
select
TOTALCOMPENSATION,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPINCOMECOMPENSATION
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1 and
HISTORICCODE <> 1
)
select
@C_NINCOME = count(*),
@C_INCOME = sum(TOTALCOMPENSATION)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
PROPERTYVALUATION,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPREALESTATE
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1 and
HISTORICCODE <> 1
)
select
@C_NREALESTATE = count(*),
@C_REALESTATEASSETS = sum(PROPERTYVALUATION)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
with CTE as
(
select
VALUEDIRECT,
row_number() over(partition by PARTIALHASH order by DATECHANGED desc) as ROWNUMBER,
PARTIALHASH
from dbo.WPSECURITIES
where
WEALTHID in (select MEMBERID from @GROUPMEMBERS) and
CONFIRMED=1
)
select
@C_NSECURITIES = count(*),
@C_SECURITIESASSETS = sum(VALUEDIRECT)
from CTE
where
ROWNUMBER = 1 or
(PARTIALHASH = '' or PARTIALHASH is null);
end try
begin catch
end catch
begin try
set @C_TOTALASSETS =coalesce( @C_REALESTATEASSETS,0)+coalesce( @C_SECURITIESASSETS,0)+coalesce( @C_BUSINESSASSETS,0)+coalesce( @C_AFFLUENCEINDICATORSASSETS,0)+coalesce(@C_OTHERASSETS,0)+coalesce( @C_INCOME,0);
set @TOTALASSETS =coalesce( @REALESTATEASSETS,0)+coalesce( @SECURITIESASSETS,0)+coalesce( @BUSINESSASSETS,0)+coalesce( @AFFLUENCEINDICATORSASSETS,0)+coalesce(@OTHERASSETS,0)+coalesce( @INCOME,0);
end try
begin catch
set @C_TOTALASSETS = 0
set @TOTALASSETS = 0
end catch