USP_REPORT_REVENUEACTUALDETAIL
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 | |
| @REVENUETYPE | nvarchar(30) | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @MEMBERSHIPPROGRAMS | nvarchar(max) | IN | |
| @PROGRAMFILTERTYPECODE | tinyint | IN |
Definition
Copy
create procedure BBDW.[USP_REPORT_REVENUEACTUALDETAIL](
@GROUP1 nvarchar(30),
@GROUP1VALUE nvarchar(100),
@GROUP2 nvarchar(30),
@GROUP2VALUE nvarchar(100),
@GROUP3 nvarchar(30),
@GROUP3VALUE nvarchar(100),
@REVENUETYPE nvarchar(30),
@STARTDATE datetime,
@ENDDATE datetime,
@MEMBERSHIPPROGRAMS nvarchar(max),
@PROGRAMFILTERTYPECODE tinyint = 0
) as
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 @MONTH smallint
declare @CALENDARMONTHYEARNAME nvarchar(15)
declare @ETHNICITY nvarchar(100)
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
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 @MONTH =
case
when @GROUP1 = 'CALENDARMONTHNAME' then @GROUP1VALUE
when @GROUP2 = 'CALENDARMONTHNAME' then @GROUP2VALUE
when @GROUP3 = 'CALENDARMONTHNAME' 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 @REVENUETYPE = 'Earned'
--Earned Revenue
select
cl.[CONSTITUENTLOOKUPID],
cl.[FULLNAME],
cl.[PRIMARYADDRESS],
cl.[PRIMARYADDRESSCITY],
case when cl.[PRIMARYADDRESSSTATE] = 'No State' then '' else cl.[PRIMARYADDRESSSTATE] end as [PRIMARYADDRESSSTATE],
cl.[PRIMARYADDRESSPOSTCODE],
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), cl.[CONSTITUENTSYSTEMID]) as CONSTITUENTID,
mp.[MEMBERSHIPPROGRAM],
r.[REVENUELOOKUPID],
r.[REVENUEAPPLICATIONAMOUNT],
d.[ACTUALDATE] as [REVENUEDATE],
'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), mr.[MEMBERSHIPSYSTEMID]) as MEMBERSHIPID,
'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), r.[REVENUESYSTEMID]) as REVENUEID
from BBDW.[v_FACT_MEMBERSHIPREVENUE] mr
left join BBDW.[v_FACT_REVENUE] r on mr.[REVENUEFACTID] = r.[REVENUEFACTID]
left join BBDW.[v_DIM_REVENUECODE] rc on r.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] mp on mr.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID]
inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = mp.[MEMBERSHIPPROGRAMSYSTEMID]
left join BBDW.[v_FACT_MEMBERSHIPTRANSACTION] mt on mr.[MEMBERSHIPTRANSACTIONFACTID] = mt.[MEMBERSHIPTRANSACTIONFACTID]
left join BBDW.[v_DIM_CONSTITUENTLOOKUP] cl on r.[CONSTITUENTDIMID] = cl.[CONSTITUENTDIMID]
left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = r.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1
left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = r.CONSTITUENTDIMID
left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID]
left join BBDW.[v_DIM_DATE] d on r.[REVENUEDATEDIMID] = d.[DATEDIMID]
where
r.[REVENUEDATEDIMID] between @STARTDATEDIMID and @ENDDATEDIMID and
((rc.[REVENUEAPPLICATIONCODE] = 5 and rc.[REVENUEAPPLICATIONTYPECODE] = 2) or (rc.[REVENUEAPPLICATIONCODE] = 18 and rc.[REVENUEAPPLICATIONTYPECODE] = 18)) and
(mp.[MEMBERSHIPPROGRAM] = @MEMBERSHIPPROGRAM or @MEMBERSHIPPROGRAM is null) and
(mp.[MEMBERSHIPLEVEL] = @MEMBERSHIPLEVEL or @MEMBERSHIPLEVEL is null) and
(mp.[MEMBERSHIPREPORTINGGROUP] = @MEMBERSHIPREPORTINGGROUP or @MEMBERSHIPREPORTINGGROUP is null) and
(cl.[PRIMARYADDRESSCOUNTRY] = @COUNTRY or @COUNTRY is null) and
(cl.[PRIMARYADDRESSSTATE] = @STATE or @STATE is null) and
(cl.[PRIMARYADDRESSCOUNTY] = @COUNTY or @COUNTY is null) and
(cl.[PRIMARYADDRESSCITY] = @CITY or @CITY is null) and
(cl.[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
(d.[CALENDARYEAR] = @CALENDARYEAR or @CALENDARYEAR is null) and
(d.[FISCALYEAR] = @FISCALYEAR or @FISCALYEAR is null) and
(d.[CALENDARMONTHYEARNAME] = @CALENDARMONTHYEARNAME or @CALENDARMONTHYEARNAME is null) and
(isnull(e.[ETHNICITY], 'No Ethnicity') = @ETHNICITY or @ETHNICITY is null)
order by cl.[KEYNAME], cl.[FIRSTNAME]
else
--Contributed Revenue
select
cl.[CONSTITUENTLOOKUPID],
cl.[FULLNAME],
cl.[PRIMARYADDRESS],
cl.[PRIMARYADDRESSCITY],
case when cl.[PRIMARYADDRESSSTATE] = 'No State' then '' else cl.[PRIMARYADDRESSSTATE] end as [PRIMARYADDRESSSTATE],
cl.[PRIMARYADDRESSPOSTCODE],
'http://www.blackbaud.com/CONSTITUENTID?CONSTITUENTID=' + CONVERT(nvarchar(36), cl.[CONSTITUENTSYSTEMID]) as CONSTITUENTID,
mp.[MEMBERSHIPPROGRAM],
r.[REVENUELOOKUPID],
r.[REVENUEAPPLICATIONAMOUNT],
d.[ACTUALDATE] as [REVENUEDATE],
'http://www.blackbaud.com/MEMBERSHIPID?MEMBERSHIPID=' + CONVERT(nvarchar(36), mr.[MEMBERSHIPSYSTEMID]) as MEMBERSHIPID,
'http://www.blackbaud.com/REVENUEID?REVENUEID=' + CONVERT(nvarchar(36), r.[REVENUESYSTEMID]) as REVENUEID
from BBDW.[v_FACT_MEMBERSHIPREVENUE] mr
left join BBDW.[v_FACT_REVENUE] r on mr.[REVENUEFACTID] = r.[REVENUEFACTID]
left join BBDW.[v_DIM_REVENUECODE] rc on r.[REVENUECODEDIMID] = rc.[REVENUECODEDIMID]
left join BBDW.[v_DIM_MEMBERSHIPPROGRAM] mp on mr.[MEMBERSHIPPROGRAMDIMID] = mp.[MEMBERSHIPPROGRAMDIMID]
inner join @MEMBERSHIPPROGRAMIDTABLE tmp on tmp.MEMBERSHIPPROGRAMSYSTEMID = mp.[MEMBERSHIPPROGRAMSYSTEMID]
left join BBDW.[v_FACT_MEMBERSHIPTRANSACTION] mt on mr.[MEMBERSHIPTRANSACTIONFACTID] = mt.[MEMBERSHIPTRANSACTIONFACTID]
left join BBDW.[v_DIM_CONSTITUENTLOOKUP] cl on r.[CONSTITUENTDIMID] = cl.[CONSTITUENTDIMID]
left join BBDW.[v_DIM_EDUCATION] de on de.[CONSTITUENTDIMID] = r.[CONSTITUENTDIMID] and de.[ISPRIMARYRECORD] = 1 and de.[EDUCATIONINSTITUTIONISAFFLIATED] = 1
left join BBDW.[v_FACT_CONSTITUENTETHNICITY] fe on fe.[CONSTITUENTDIMID] = r.CONSTITUENTDIMID
left join BBDW.[v_DIM_ETHNICITY] e on fe.[ETHNICITYDIMID] = e.[ETHNICITYDIMID]
left join BBDW.[v_DIM_DATE] d on r.[REVENUEDATEDIMID] = d.[DATEDIMID]
where
r.[REVENUEDATEDIMID] between @STARTDATEDIMID and @ENDDATEDIMID and
rc.[REVENUEAPPLICATIONCODE] = 0 and rc.[REVENUEAPPLICATIONTYPECODE] = 0 and
(mp.[MEMBERSHIPPROGRAM] = @MEMBERSHIPPROGRAM or @MEMBERSHIPPROGRAM is null) and
(mp.[MEMBERSHIPLEVEL] = @MEMBERSHIPLEVEL or @MEMBERSHIPLEVEL is null) and
(mp.[MEMBERSHIPREPORTINGGROUP] = @MEMBERSHIPREPORTINGGROUP or @MEMBERSHIPREPORTINGGROUP is null) and
(cl.[PRIMARYADDRESSCOUNTRY] = @COUNTRY or @COUNTRY is null) and
(cl.[PRIMARYADDRESSSTATE] = @STATE or @STATE is null) and
(cl.[PRIMARYADDRESSCOUNTY] = @COUNTY or @COUNTY is null) and
(cl.[PRIMARYADDRESSCITY] = @CITY or @CITY is null) and
(cl.[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
(d.[CALENDARYEAR] = @CALENDARYEAR or @CALENDARYEAR is null) and
(d.[FISCALYEAR] = @FISCALYEAR or @FISCALYEAR is null) and
(d.[CALENDARMONTHYEARNAME] = @CALENDARMONTHYEARNAME or @CALENDARMONTHYEARNAME is null) and
(isnull(e.[ETHNICITY], 'No Ethnicity') = @ETHNICITY or @ETHNICITY is null)
order by cl.[KEYNAME], cl.[FIRSTNAME]