USP_REPORT_MEMBERSHIPACTIVITYDETAIL
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GROUP1 | nvarchar(30) | IN | |
| @GROUP1VALUE | nvarchar(100) | IN | |
| @GROUP2 | nvarchar(30) | IN | |
| @GROUP2VALUE | nvarchar(100) | IN | |
| @GROUP3 | nvarchar(30) | IN | |
| @GROUP3VALUE | nvarchar(100) | IN | |
| @DATALABEL | nvarchar(30) | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @MEMBERSHIPPROGRAMS | nvarchar(max) | IN | |
| @PROGRAMFILTERTYPECODE | tinyint | IN |
Definition
Copy
create procedure BBDW.[USP_REPORT_MEMBERSHIPACTIVITYDETAIL](
@GROUP1 nvarchar(30),
@GROUP1VALUE nvarchar(100),
@GROUP2 nvarchar(30),
@GROUP2VALUE nvarchar(100),
@GROUP3 nvarchar(30),
@GROUP3VALUE nvarchar(100),
@DATALABEL nvarchar(30),
@STARTDATE datetime,
@ENDDATE datetime,
@MEMBERSHIPPROGRAMS nvarchar(max),
@PROGRAMFILTERTYPECODE tinyint = 0
)
with execute as owner as
if @DATALABEL = 'LAPSED_GRACE'
set @DATALABEL = 'Lapsed Grace'
set @MEMBERSHIPPROGRAMS = REPLACE(@MEMBERSHIPPROGRAMS, '<', '<');
set @MEMBERSHIPPROGRAMS = REPLACE(@MEMBERSHIPPROGRAMS, '>', '>');
declare @MEMBERSHIPPROGRAMIDTABLE table (MEMBERSHIPPROGRAMSYSTEMID uniqueidentifier)
-- 0 = All, 1 = Selected, 2 = None
if @PROGRAMFILTERTYPECODE = 0
begin
insert into @MEMBERSHIPPROGRAMIDTABLE (MEMBERSHIPPROGRAMSYSTEMID)
select distinct [MEMBERSHIPPROGRAMSYSTEMID]
from BBDW.[DIM_MEMBERSHIPPROGRAM]
where MEMBERSHIPPROGRAMSYSTEMID <> '00000000-0000-0000-0000-000000000000'
and MEMBERSHIPPROGRAMISACTIVE = 1
end
else if @PROGRAMFILTERTYPECODE = 1
begin
insert into @MEMBERSHIPPROGRAMIDTABLE (MEMBERSHIPPROGRAMSYSTEMID)
select [MEMBERSHIPPROGRAMID] from BBDW.[UFN_MEMBERSHIPPROGRAMS_FROMITEMLISTXML] (@MEMBERSHIPPROGRAMS);
end
if @STARTDATE is null
set @STARTDATE = '7/4/1776'
if @ENDDATE is null
set @ENDDATE = '12/31/5999'
declare @STARTDATEDIMID int
declare @ENDDATEDIMID int
set @STARTDATEDIMID = (year(@STARTDATE) * 10000) + (month(@STARTDATE) * 100) + day(@STARTDATE)
set @ENDDATEDIMID = (year(@ENDDATE) * 10000) + (month(@ENDDATE) * 100) + day(@ENDDATE)
declare @MEMBERSHIPPROGRAM nvarchar(100)
declare @MEMBERSHIPLEVEL nvarchar(100)
declare @MEMBERSHIPREPORTINGGROUP nvarchar(100)
declare @COUNTRY nvarchar(100)
declare @STATE nvarchar(100)
declare @COUNTY nvarchar(100)
declare @CITY nvarchar(50)
declare @POSTCODE nvarchar(12)
declare @CLASSOF int
declare @DEGREE nvarchar(150)
declare @FISCALYEAR smallint
declare @CALENDARYEAR smallint
declare @CALENDARMONTHYEARNAME nvarchar(15)
declare @ETHNICITY nvarchar(100)
set @MEMBERSHIPPROGRAM =
case
when @GROUP1 = 'MEMBERSHIPPROGRAM' then @GROUP1VALUE
when @GROUP2 = 'MEMBERSHIPPROGRAM' then @GROUP2VALUE
when @GROUP3 = 'MEMBERSHIPPROGRAM' then @GROUP3VALUE
else null
end
set @MEMBERSHIPLEVEL =
case
when @GROUP1 = 'MEMBERSHIPLEVEL' then @GROUP1VALUE
when @GROUP2 = 'MEMBERSHIPLEVEL' then @GROUP2VALUE
when @GROUP3 = 'MEMBERSHIPLEVEL' then @GROUP3VALUE
else null
end
set @MEMBERSHIPREPORTINGGROUP =
case
when @GROUP1 = 'MEMBERSHIPREPORTINGGROUP' then @GROUP1VALUE
when @GROUP2 = 'MEMBERSHIPREPORTINGGROUP' then @GROUP2VALUE
when @GROUP3 = 'MEMBERSHIPREPORTINGGROUP' then @GROUP3VALUE
else null
end
set @COUNTRY =
case
when @GROUP1 = 'COUNTRY' then @GROUP1VALUE
when @GROUP2 = 'COUNTRY' then @GROUP2VALUE
when @GROUP3 = 'COUNTRY' then @GROUP3VALUE
else null
end
set @CITY =
case
when @GROUP1 = 'CITY' then @GROUP1VALUE
when @GROUP2 = 'CITY' then @GROUP2VALUE
when @GROUP3 = 'CITY' then @GROUP3VALUE
else null
end
set @COUNTY =
case
when @GROUP1 = 'COUNTY' then @GROUP1VALUE
when @GROUP2 = 'COUNTY' then @GROUP2VALUE
when @GROUP3 = 'COUNTY' then @GROUP3VALUE
else null
end
set @POSTCODE =
case
when @GROUP1 = 'POSTCODE' then @GROUP1VALUE
when @GROUP2 = 'POSTCODE' then @GROUP2VALUE
when @GROUP3 = 'POSTCODE' then @GROUP3VALUE
else null
end
set @CLASSOF =
case
when @GROUP1 = 'CLASSOF' then @GROUP1VALUE
when @GROUP2 = 'CLASSOF' then @GROUP2VALUE
when @GROUP3 = 'CLASSOF' then @GROUP3VALUE
else null
end
set @DEGREE =
case
when @GROUP1 = 'EDUCATIONDEGREE' then @GROUP1VALUE
when @GROUP2 = 'EDUCATIONDEGREE' then @GROUP2VALUE
when @GROUP3 = 'EDUCATIONDEGREE' then @GROUP3VALUE
else null
end
set @FISCALYEAR =
case
when @GROUP1 = 'FISCALYEAR' then @GROUP1VALUE
when @GROUP2 = 'FISCALYEAR' then @GROUP2VALUE
when @GROUP3 = 'FISCALYEAR' then @GROUP3VALUE
else null
end
set @CALENDARYEAR =
case
when @GROUP1 = 'CALENDARYEAR' then @GROUP1VALUE
when @GROUP2 = 'CALENDARYEAR' then @GROUP2VALUE
when @GROUP3 = 'CALENDARYEAR' then @GROUP3VALUE
else null
end
set @CALENDARMONTHYEARNAME =
case
when @GROUP1 = 'CALENDARMONTHYEARNAME' then @GROUP1VALUE
when @GROUP2 = 'CALENDARMONTHYEARNAME' then @GROUP2VALUE
when @GROUP3 = 'CALENDARMONTHYEARNAME' then @GROUP3VALUE
else null
end
set @ETHNICITY =
case
when @GROUP1 = 'ETHNICITY' then @GROUP1VALUE
when @GROUP2 = 'ETHNICITY' then @GROUP2VALUE
when @GROUP3 = 'ETHNICITY' then @GROUP3VALUE
else null
end
if @DATALABEL = 'MEMBERSHIPS' and @CALENDARMONTHYEARNAME is not null
begin
set @STARTDATEDIMID = (select min(DATEDIMID) from bbdw.DIM_DATE where CALENDARMONTHYEARNAME = @CALENDARMONTHYEARNAME)
set @ENDDATEDIMID = (select max(DATEDIMID) from bbdw.DIM_DATE where CALENDARMONTHYEARNAME = @CALENDARMONTHYEARNAME)
end
if @DATALABEL = 'MEMBERSHIPS'
begin
select distinct
dcl.[CONSTITUENTLOOKUPID],
dcl.[FULLNAME],
dcl.[PRIMARYADDRESS],
dcl.[PRIMARYADDRESSCITY],
case when dcl.[PRIMARYADDRESSSTATE] = 'No State' then '' else dcl.[PRIMARYADDRESSSTATE] end as [PRIMARYADDRESSSTATE],
dcl.[PRIMARYADDRESSPOSTCODE],
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), dcl.[CONSTITUENTSYSTEMID]) as CONSTITUENTID,
dmp.[MEMBERSHIPPROGRAM],
dm.[JOINDATE],
dm.[EXPIRATIONDATE],
'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), dm.[MEMBERSHIPSYSTEMID]) as MEMBERSHIPID,
dcl.[KEYNAME],
dcl.[FIRSTNAME]
from BBDW.[v_FACT_MEMBERSHIPTRANSACTION] fmt
left join BBDW.[v_DIM_MEMBERSHIP] dm on dm.[MEMBERSHIPDIMID] = fmt.[MEMBERSHIPDIMID]
left join BBDW.[v_DIM_MEMBERSHIPSTATUS] ms on ms.[MEMBERSHIPSTATUSDIMID] = fmt.[MEMBERSHIPSTATUSDIMID]
left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] dmp on dmp.[MEMBERSHIPPROGRAMDIMID] = dm.[MEMBERSHIPPROGRAMDIMID]
inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = dmp.[MEMBERSHIPPROGRAMSYSTEMID]
left join BBDW.[v_DIM_MEMBERSHIPACTION] dma on dma.[MEMBERSHIPACTIONDIMID] = fmt.[MEMBERSHIPACTIONDIMID]
left join BBDW.[v_FACT_MEMBER] fmp on fmp.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID]
inner join BBDW.[v_DIM_MEMBERFLAG] dmf on fmp.[MEMBERFLAGDIMID] = dmf.[MEMBERFLAGDIMID] and dmf.[MEMBERISPRIMARY] = 1
left join BBDW.[v_DIM_CONSTITUENTLOOKUP] dcl on dcl.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID]
left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1
left join BBDW.[v_DIM_DATE] dd on dd.[DATEDIMID] = fmt.[MEMBERSHIPTRANSACTIONDATEDIMID]
left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = fmp.CONSTITUENTDIMID
left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID]
where
ms.[MEMBERSHIPSTATUSCODE] in (0,99) and
fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] <= @ENDDATEDIMID and
fmt.[NEXTMEMBERSHIPTRANSACTIONDATEDIMID] >= @STARTDATEDIMID and
(dmp.[MEMBERSHIPPROGRAM] = @MEMBERSHIPPROGRAM or @MEMBERSHIPPROGRAM is null) and
(dmp.[MEMBERSHIPLEVEL] = @MEMBERSHIPLEVEL or @MEMBERSHIPLEVEL is null) and
(dmp.[MEMBERSHIPREPORTINGGROUP] = @MEMBERSHIPREPORTINGGROUP or @MEMBERSHIPREPORTINGGROUP is null) and
(dcl.[PRIMARYADDRESSCOUNTRY] = @COUNTRY or @COUNTRY is null) and
(dcl.[PRIMARYADDRESSSTATE] = @STATE or @STATE is null) and
(dcl.[PRIMARYADDRESSCOUNTY] = @COUNTY or @COUNTY is null) and
(dcl.[PRIMARYADDRESSCITY] = @CITY or @CITY is null) and
(dcl.[PRIMARYADDRESSPOSTCODE] = @POSTCODE or @POSTCODE is null) and
(isnull(de.[CLASSOF], 0) = @CLASSOF or @CLASSOF is null) and
((case
when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then 'No Educational Degree'
when de.[ACADEMICCATALOGDEGREE] <> 'No Academic Catalog Degree' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end ) = @DEGREE or @DEGREE is null) and
(isnull(e.[ETHNICITY], 'No Ethnicity') = @ETHNICITY or @ETHNICITY is null) and
(dd.[CALENDARYEAR] = @CALENDARYEAR or @CALENDARYEAR is null) and
(dd.[FISCALYEAR] = @FISCALYEAR or @FISCALYEAR is null)
order by dcl.[KEYNAME], dcl.[FIRSTNAME]
end
else
begin
select
dcl.[CONSTITUENTLOOKUPID],
dcl.[FULLNAME],
dcl.[PRIMARYADDRESS],
dcl.[PRIMARYADDRESSCITY],
case when dcl.[PRIMARYADDRESSSTATE] = 'No State' then '' else dcl.[PRIMARYADDRESSSTATE] end as [PRIMARYADDRESSSTATE],
dcl.[PRIMARYADDRESSPOSTCODE],
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), dcl.[CONSTITUENTSYSTEMID]) as [CONSTITUENTID],
dmp.[MEMBERSHIPPROGRAM],
dm.[JOINDATE],
dm.[EXPIRATIONDATE],
'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), dm.[MEMBERSHIPSYSTEMID]) as [MEMBERSHIPID],
dcl.[KEYNAME],
dcl.[FIRSTNAME]
from BBDW.[v_FACT_MEMBERSHIPTRANSACTION] fmt
left join BBDW.[v_DIM_MEMBERSHIP] dm on dm.[MEMBERSHIPDIMID] = fmt.[MEMBERSHIPDIMID]
left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] dmp on dmp.[MEMBERSHIPPROGRAMDIMID] = dm.[MEMBERSHIPPROGRAMDIMID]
inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = dmp.[MEMBERSHIPPROGRAMSYSTEMID]
left join BBDW.[v_DIM_MEMBERSHIPACTION] dma on dma.[MEMBERSHIPACTIONDIMID] = fmt.[MEMBERSHIPACTIONDIMID]
left join BBDW.[v_FACT_MEMBER] fmp on fmp.[MEMBERSHIPDIMID] = dm.[MEMBERSHIPDIMID]
inner join BBDW.[v_DIM_MEMBERFLAG] dmf on fmp.[MEMBERFLAGDIMID] = dmf.[MEMBERFLAGDIMID] and dmf.[MEMBERISPRIMARY] = 1
left join BBDW.[v_DIM_CONSTITUENTLOOKUP] dcl on dcl.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID]
left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = fmp.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1
left join BBDW.[v_DIM_DATE] dd on dd.[DATEDIMID] = fmt.[MEMBERSHIPTRANSACTIONDATEDIMID]
left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = fmp.CONSTITUENTDIMID
left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID]
where
fmt.[MEMBERSHIPTRANSACTIONDATEDIMID] between @STARTDATEDIMID and @ENDDATEDIMID and
dma.[MEMBERSHIPACTION] = @DATALABEL and
(dmp.[MEMBERSHIPPROGRAM] = @MEMBERSHIPPROGRAM or @MEMBERSHIPPROGRAM is null) and
(dmp.[MEMBERSHIPLEVEL] = @MEMBERSHIPLEVEL or @MEMBERSHIPLEVEL is null) and
(dmp.[MEMBERSHIPREPORTINGGROUP] = @MEMBERSHIPREPORTINGGROUP or @MEMBERSHIPREPORTINGGROUP is null) and
(dcl.[PRIMARYADDRESSCOUNTRY] = @COUNTRY or @COUNTRY is null) and
(dcl.[PRIMARYADDRESSSTATE] = @STATE or @STATE is null) and
(dcl.[PRIMARYADDRESSCOUNTY] = @COUNTY or @COUNTY is null) and
(dcl.[PRIMARYADDRESSCITY] = @CITY or @CITY is null) and
(dcl.[PRIMARYADDRESSPOSTCODE] = @POSTCODE or @POSTCODE is null) and
(isnull(de.[CLASSOF], 0) = @CLASSOF or @CLASSOF is null) and
((case
when de.[ACADEMICCATALOGDEGREE] is null and de.[EDUCATIONDEGREE] is null then 'No Educational Degree'
when de.[ACADEMICCATALOGDEGREE] <> 'No Academic Catalog Degree' then de.[ACADEMICCATALOGDEGREE]
else de.[EDUCATIONDEGREE]
end ) = @DEGREE or @DEGREE is null) and
(isnull(e.[ETHNICITY], 'No Ethnicity') = @ETHNICITY or @ETHNICITY is null) and
(dd.[CALENDARYEAR] = @CALENDARYEAR or @CALENDARYEAR is null) and
(dd.[FISCALYEAR] = @FISCALYEAR or @FISCALYEAR is null) and
(dd.[CALENDARMONTHYEARNAME] = @CALENDARMONTHYEARNAME or @CALENDARMONTHYEARNAME is null)
order by dcl.[KEYNAME], dcl.[FIRSTNAME]
end