v_FACT_RECURRINGGIFTDEVELOPMENTHISTORY

The recurring gift development history fact table contains development history of recurring gifts.

Fields

Field	Field Type	Null	Description
RECURRINGGIFTDEVELOPMENTHISTORYFACTID	int		BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[RECURRINGGIFTDEVELOPMENTHISTORYFACTID]
RECURRINGGIFTDEVELOPMENTDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[RECURRINGGIFTDEVELOPMENTDIMID]
REVENUESCHEDULEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[REVENUESCHEDULEDIMID]
REVENUESCHEDULESTARTDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[REVENUESCHEDULESTARTDATEDIMID]
REVENUESCHEDULEENDDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[REVENUESCHEDULEENDDATEDIMID]
RECURRINGGIFTDEVELOPMENTHISTORYDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[RECURRINGGIFTDEVELOPMENTHISTORYDATEDIMID]
AMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[AMOUNT]
PREVIOUSAMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[PREVIOUSAMOUNT]
BASEPREVIOUSAMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[BASEPREVIOUSAMOUNT]
BASEAMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[BASEAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[BASECURRENCYDIMID]
REVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
REVENUESEQUENCEID	int	yes	BBDW.[FACT_REVENUE].[REVENUESEQUENCEID]
REVENUECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECODEDIMID]
REVENUEFLAGDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFLAGDIMID]
REVENUECHANNELDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECHANNELDIMID]
REVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
POSTDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[POSTDATEDIMID]
CONSTITUENTDIMID	int	yes	BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
CONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
MARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
MARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
MARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
GLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
CONSTITUENTINFORMATIONDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
CONSTITUENTAGE	int	yes	BBDW.[DIM_CONSTITUENT].[AGE]
CONSTITUENTADDRESSDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID]
CONSTITUENTADDRESSDETAILDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID]
CONSTITUENTADDRESSFLAGDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID]
CONSTITUENTADDRESSTYPEDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID]
CONSTITUENTDEMOGRAPHICDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID]
SEGMENTATIONDATEDIMID	int	yes	BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
APPEALDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALDIMID]
APPEALSTARTDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
APPEALENDDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
APPEALWEEKDIMID	int	yes	The difference in weeks between the appeal start date and the recurring gift installment date. Anything over 104 weeks is labeled 105+.
MARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the recurring gift installment date. Anything over 104 weeks is labeled 105+.
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_RECURRINGGIFTDEVELOPMENTHISTORY] as
        select
          rgdh.[RECURRINGGIFTDEVELOPMENTHISTORYFACTID], 
          rgdh.[RECURRINGGIFTDEVELOPMENTDIMID], 
          rgdh.[REVENUESCHEDULEDIMID], 
          rgdh.[REVENUESCHEDULESTARTDATEDIMID], 
          rgdh.[REVENUESCHEDULEENDDATEDIMID], 
          rgdh.[RECURRINGGIFTDEVELOPMENTHISTORYDATEDIMID], 
          rgdh.[AMOUNT], 
          rgdh.[PREVIOUSAMOUNT], 
          rgdh.[BASEPREVIOUSAMOUNT], 
          rgdh.[BASEAMOUNT], 
          rgdh.[BASECURRENCYDIMID],
          r.[FINANCIALTRANSACTIONLINEITEMFACTID] as [REVENUEFACTID], 
          r.[FINANCIALTRANSACTIONSEQUENCEID] as [REVENUESEQUENCEID], 
          r.[REVENUECODEDIMID],
          r.[REVENUEFLAGDIMID], 
          r.[REVENUECHANNELDIMID], 
          r.[FINANCIALTRANSACTIONDATEDIMID] as [REVENUEDATEDIMID], 
          r.[FINANCIALTRANSACTIONPOSTDATEDIMID] as [POSTDATEDIMID], 
          r.[CONSTITUENTDIMID], 
          r.[CONSTITUENTSYSTEMID], 
          r.[MARKETINGSEGMENTDIMID], 
          r.[MARKETINGSOURCECODEDIMID], 
          r.[MARKETINGCONSTITUENTFACTID], 
          r.[GLREVENUECATEGORYDIMID], 
          c.[CONSTITUENTINFORMATIONDIMID], 
          c.[AGE] as [CONSTITUENTAGE], 
          c.[CONSTITUENTADDRESSDIMID], 
          c.[CONSTITUENTADDRESSDETAILDIMID],
          c.[CONSTITUENTADDRESSFLAGDIMID],
          c.[CONSTITUENTADDRESSTYPEDIMID],    
          c.[CONSTITUENTDEMOGRAPHICDIMID], 
          ms.[SEGMENTATIONDATEDIMID], 
          a.[APPEALDIMID], 
          a.[APPEALSTARTDATEDIMID], 
          a.[APPEALENDDATEDIMID],     
          case 
            when a.[APPEALSTARTDATE] is null then 0 
            when a.[APPEALSTARTDATE] > r.[FINANCIALTRANSACTIONDATE] then 0 
            when datediff(ww, a.[APPEALSTARTDATE], r.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
            else datediff(ww, a.[APPEALSTARTDATE], r.[FINANCIALTRANSACTIONDATE]) + 1 
          end as [APPEALWEEKDIMID], 
          case 
            when ms.[SEGMENTATIONDATE] IS NULL then 0 
            when ms.[SEGMENTATIONDATE] > r.[FINANCIALTRANSACTIONDATE] then 0       
            when datediff(ww, ms.[SEGMENTATIONDATE], r.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
            else datediff(ww, ms.[SEGMENTATIONDATE], r.[FINANCIALTRANSACTIONDATE]) + 1 
          end as [MARKETINGRESPONSEWEEKDIMID],
          rgdh.[FINANCIALTRANSACTIONFACTID],
          rgdh.[FINANCIALTRANSACTIONLINEITEMFACTID]
        from 
          BBDW.[FACT_RECURRINGGIFTDEVELOPMENTHISTORY] rgdh
          left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] r
            on rgdh.[REVENUEFACTID] = r.[FINANCIALTRANSACTIONLINEITEMFACTID]
          left join BBDW.DIM_CONSTITUENT c
            on r.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
          left join BBDW.DIM_MARKETINGSEGMENT ms
            on r.[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID]
          left join BBDW.DIM_APPEAL a
            on r.[APPEALDIMID] = a.[APPEALDIMID]