v_FACT_RECURRINGGIFTINSTALLMENTPAYMENT

The recurring gift installment payment fact table contains information about recurring gift installment payments.

Fields

Field	Field Type	Null	Description
RECURRINGGIFTINSTALLMENTPAYMENTAMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[RECURRINGGIFTINSTALLMENTPAYMENTAMOUNT]
BASERECURRINGGIFTINSTALLMENTPAYMENTAMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[BASERECURRINGGIFTINSTALLMENTPAYMENTAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[BASECURRENCYDIMID]
PAYMENTREVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
PAYMENTREVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
PAYMENTPOSTDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[PAYMENTPOSTDATEDIMID]
RECURRINGGIFTINSTALLMENTFACTID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENT].[RECURRINGGIFTINSTALLMENTFACTID]
REVENUESCHEDULEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENT].[REVENUESCHEDULEDIMID]
REVENUESCHEDULESTARTDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENT].[REVENUESCHEDULESTARTDATEDIMID]
REVENUESCHEDULEENDDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENT].[REVENUESCHEDULEENDDATEDIMID]
RECURRINGGIFTINSTALLMENTDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENT].[RECURRINGGIFTINSTALLMENTDATEDIMID]
RECURRINGGIFTFLAGDIMID	int	yes	BBDW.[DIM_RECURRINGGIFTFLAG].[RECURRINGGIFTFLAGDIMID]
RECURRINGGIFTINSTALLMENTSTATUSDIMID	int		Calculation that determines when the recurring gift installment will occur relative to the current date. 0 = Past Due, 1 = Up to 1 year, 2 = 1 - 5 years, 3 = 5+ years.
RECURRINGGIFTINSTALLMENTPAYMENTLAGDAYS	int	yes	The difference in days between the recurring gift installment date and the recurring gift installment payment date.
ISINSTALLMENTPAYMENTLATE	int		True if the recurring gift installment payment date is after the recurring gift installment date.
PLEDGEREVENUEFACTID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFACTID]
PLEDGEREVENUESEQUENCEID	int	yes	BBDW.[FACT_REVENUE].[REVENUESEQUENCEID]
PLEDGEREVENUECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECODEDIMID]
PLEDGEREVENUEFLAGDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEFLAGDIMID]
PLEDGEREVENUECHANNELDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUECHANNELDIMID]
PLEDGEREVENUEDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[REVENUEDATEDIMID]
PLEDGEPOSTDATEDIMID	int	yes	BBDW.[FACT_REVENUE].[POSTDATEDIMID]
PLEDGECONSTITUENTDIMID	int	yes	BBDW.[FACT_REVENUE].[CONSTITUENTDIMID]
PLEDGECONSTITUENTSYSTEMID	uniqueidentifier	yes	BBDW.[FACT_REVENUE].[CONSTITUENTSYSTEMID]
PLEDGEMARKETINGSEGMENTDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSEGMENTDIMID]
PLEDGEMARKETINGSOURCECODEDIMID	int	yes	BBDW.[FACT_REVENUE].[MARKETINGSOURCECODEDIMID]
PLEDGEMARKETINGCONSTITUENTFACTID	bigint	yes	BBDW.[FACT_REVENUE].[MARKETINGCONSTITUENTFACTID]
PLEDGEGLREVENUECATEGORYDIMID	int	yes	BBDW.[FACT_REVENUE].[GLREVENUECATEGORYDIMID]
PLEDGECONSTITUENTINFORMATIONDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTINFORMATIONDIMID]
PLEDGECONSTITUENTAGE	int	yes	BBDW.[DIM_CONSTITUENT].[AGE]
PLEDGECONSTITUENTADDRESSDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDIMID]
PLEDGECONSTITUENTADDRESSDETAILDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSDETAILDIMID]
PLEDGECONSTITUENTADDRESSFLAGDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSFLAGDIMID]
PLEDGECONSTITUENTADDRESSTYPEDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTADDRESSTYPEDIMID]
PLEDGECONSTITUENTDEMOGRAPHICDIMID	int	yes	BBDW.[DIM_CONSTITUENT].[CONSTITUENTDEMOGRAPHICDIMID]
PLEDGESEGMENTATIONDATEDIMID	int	yes	BBDW.[DIM_MARKETINGSEGMENT].[SEGMENTATIONDATEDIMID]
PLEDGEAPPEALDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALDIMID]
PLEDGEAPPEALSTARTDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALSTARTDATEDIMID]
PLEDGEAPPEALENDDATEDIMID	int	yes	BBDW.[DIM_APPEAL].[APPEALENDDATEDIMID]
PAYMENTAPPEALWEEKDIMID	int	yes	The difference in weeks between the appeal start date and the recurring gift installment payment date. Anything over 104 weeks is labeled 105+.
PAYMENTMARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the recurring gift installment payment date. Anything over 104 weeks is labeled 105+.
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[FINANCIALTRANSACTIONLINEITEMFACTID]
ACTIVITYTYPE	nvarchar(8)	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT].[ACTIVITYTYPE]

Definition

Copy


      CREATE view [BBDW].[v_FACT_RECURRINGGIFTINSTALLMENTPAYMENT] as
        select
          rgip.[RECURRINGGIFTINSTALLMENTPAYMENTAMOUNT], 
          rgip.[BASERECURRINGGIFTINSTALLMENTPAYMENTAMOUNT], 
          rgip.[BASECURRENCYDIMID], 
          payment.[FINANCIALTRANSACTIONLINEITEMFACTID] as [PAYMENTREVENUEFACTID],
          payment.[FINANCIALTRANSACTIONDATEDIMID] as [PAYMENTREVENUEDATEDIMID], 
          payment.[FINANCIALTRANSACTIONPOSTDATEDIMID] as [PAYMENTPOSTDATEDIMID],
          rgi.[RECURRINGGIFTINSTALLMENTFACTID], 
          rgi.[REVENUESCHEDULEDIMID], 
          rgi.[REVENUESCHEDULESTARTDATEDIMID], 
          rgi.[REVENUESCHEDULEENDDATEDIMID], 
          rgi.[RECURRINGGIFTINSTALLMENTDATEDIMID],     
          rgf.[RECURRINGGIFTFLAGDIMID], 
          case
            when rgi.[RECURRINGGIFTINSTALLMENTDATE] < d.[ACTUALDATE] then 0 
            when dateadd(yy, -1, rgi.[RECURRINGGIFTINSTALLMENTDATE]) < d.[ACTUALDATE] then 1 
            when dateadd(yy, -5, rgi.[RECURRINGGIFTINSTALLMENTDATE]) < d.[ACTUALDATE] then 2 
            else 3 
          end as [RECURRINGGIFTINSTALLMENTSTATUSDIMID], 
          datediff(dd, rgi.[RECURRINGGIFTINSTALLMENTDATE], payment.[FINANCIALTRANSACTIONDATE]) as [RECURRINGGIFTINSTALLMENTPAYMENTLAGDAYS],
          case 
            when datediff(dd, rgi.[RECURRINGGIFTINSTALLMENTDATE], payment.[FINANCIALTRANSACTIONDATE]) > 0 then 1 
            else 0 
          end as [ISINSTALLMENTPAYMENTLATE], 
          pledge.[FINANCIALTRANSACTIONLINEITEMFACTID] as [PLEDGEREVENUEFACTID],  
          pledge.[FINANCIALTRANSACTIONSEQUENCEID] as [PLEDGEREVENUESEQUENCEID],
          pledge.[REVENUECODEDIMID] as [PLEDGEREVENUECODEDIMID], 
          pledge.[REVENUEFLAGDIMID] as [PLEDGEREVENUEFLAGDIMID], 
          pledge.[REVENUECHANNELDIMID] as [PLEDGEREVENUECHANNELDIMID],  
          pledge.[FINANCIALTRANSACTIONDATEDIMID] as [PLEDGEREVENUEDATEDIMID], 
          pledge.[FINANCIALTRANSACTIONPOSTDATEDIMID] as [PLEDGEPOSTDATEDIMID], 
          pledge.[CONSTITUENTDIMID] as [PLEDGECONSTITUENTDIMID], 
          pledge.[CONSTITUENTSYSTEMID] as [PLEDGECONSTITUENTSYSTEMID], 
          pledge.[MARKETINGSEGMENTDIMID] as [PLEDGEMARKETINGSEGMENTDIMID], 
          pledge.[MARKETINGSOURCECODEDIMID] as [PLEDGEMARKETINGSOURCECODEDIMID], 
          pledge.[MARKETINGCONSTITUENTFACTID] as [PLEDGEMARKETINGCONSTITUENTFACTID], 
          pledge.[GLREVENUECATEGORYDIMID] as [PLEDGEGLREVENUECATEGORYDIMID], 
          c.[CONSTITUENTINFORMATIONDIMID] as [PLEDGECONSTITUENTINFORMATIONDIMID], 
          c.[AGE] as [PLEDGECONSTITUENTAGE], 
          c.[CONSTITUENTADDRESSDIMID] as [PLEDGECONSTITUENTADDRESSDIMID], 
          c.[CONSTITUENTADDRESSDETAILDIMID] as [PLEDGECONSTITUENTADDRESSDETAILDIMID],
          c.[CONSTITUENTADDRESSFLAGDIMID] as [PLEDGECONSTITUENTADDRESSFLAGDIMID],
          c.[CONSTITUENTADDRESSTYPEDIMID] as [PLEDGECONSTITUENTADDRESSTYPEDIMID],
          c.[CONSTITUENTDEMOGRAPHICDIMID] as [PLEDGECONSTITUENTDEMOGRAPHICDIMID], 
          ms.[SEGMENTATIONDATEDIMID] as [PLEDGESEGMENTATIONDATEDIMID], 
          a.[APPEALDIMID] as [PLEDGEAPPEALDIMID], 
          a.[APPEALSTARTDATEDIMID] as [PLEDGEAPPEALSTARTDATEDIMID], 
          a.[APPEALENDDATEDIMID] as [PLEDGEAPPEALENDDATEDIMID], 
          case 
            when a.[APPEALSTARTDATE] is null then 0 
            when a.[APPEALSTARTDATE] > payment.[FINANCIALTRANSACTIONDATE] then 0 
            when datediff(ww, a.[APPEALSTARTDATE], payment.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
            else datediff(ww, a.[APPEALSTARTDATE], payment.[FINANCIALTRANSACTIONDATE]) + 1 
          end as [PAYMENTAPPEALWEEKDIMID], 
          case 
            when ms.[SEGMENTATIONDATE] is null then 0 
            when ms.[SEGMENTATIONDATE] > payment.[FINANCIALTRANSACTIONDATE] then 0 
            when datediff(ww, ms.[SEGMENTATIONDATE], payment.[FINANCIALTRANSACTIONDATE]) > 104 then 105 
            else datediff(ww, ms.[SEGMENTATIONDATE], payment.[FINANCIALTRANSACTIONDATE]) + 1 
          end as [PAYMENTMARKETINGRESPONSEWEEKDIMID],
          rgip.[FINANCIALTRANSACTIONFACTID],
          rgip.[FINANCIALTRANSACTIONLINEITEMFACTID],
          rgip.[ACTIVITYTYPE]  
        from
          BBDW.[FACT_RECURRINGGIFTINSTALLMENTPAYMENT] rgip
          inner join BBDW.[DIM_DATE] d on d.[ISCURRENTDATE] = 1 
          left join BBDW.[FACT_RECURRINGGIFTINSTALLMENT] rgi
            on rgi.[RECURRINGGIFTINSTALLMENTFACTID] = rgip.[RECURRINGGIFTINSTALLMENTFACTID]
          left join BBDW.[DIM_RECURRINGGIFTFLAG] rgf
            on rgi.[ISFIRSTINSTALLMENT] = rgf.[ISFIRSTINSTALLMENT] and 
               rgi.[ISLASTINSTALLMENT] = rgf.[ISLASTINSTALLMENT] and 
               rgi.[ISMISSEDINSTALLMENT] = rgf.[ISMISSEDINSTALLMENT] and 
               rgi.[ISFUTUREINSTALLMENT] = rgf.[ISFUTUREINSTALLMENT]         
          left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] payment 
            on rgip.[REVENUEFACTID] = payment.[FINANCIALTRANSACTIONLINEITEMFACTID]
          left join BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM] pledge 
            on rgi.[REVENUEFACTID] = pledge.[FINANCIALTRANSACTIONLINEITEMFACTID]
          left join BBDW.[DIM_CONSTITUENT] c
            on pledge.[CONSTITUENTDIMID] = c.[CONSTITUENTDIMID]
          left join BBDW.[DIM_MARKETINGSEGMENT] ms
            on pledge.[MARKETINGSEGMENTDIMID] = ms.[MARKETINGSEGMENTDIMID]
          left join BBDW.[DIM_APPEAL] a
            on pledge.[APPEALDIMID] = a.[APPEALDIMID];