v_FACT_RECURRINGGIFTINSTALLMENTWRITEOFF

The recurring gift installment write-off fact table contains information about recurring gift installments write-off.

Fields

Field	Field Type	Null	Description
RECURRINGGIFTINSTALLMENTWRITEOFFFACTID	int		BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF].[RECURRINGGIFTINSTALLMENTWRITEOFFFACTID]
WRITEOFFDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF].[WRITEOFFDIMID]
WRITEOFFDATEDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF].[WRITEOFFDATEDIMID]
WRITEOFFAMOUNT	money		BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF].[WRITEOFFAMOUNT]
BASECURRENCYDIMID	int	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF].[BASECURRENCYDIMID]
BASEWRITEOFFAMOUNT	money	yes	BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF].[BASEWRITEOFFAMOUNT]
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]
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 installment date. Anything over 104 weeks is labeled 105+.
MARKETINGRESPONSEWEEKDIMID	int	yes	The difference in weeks between the marketing effort date and the installment date. Anything over 104 weeks is labeled 105+.
FINANCIALTRANSACTIONFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONFACTID]
FINANCIALTRANSACTIONLINEITEMFACTID	int	yes	BBDW.[FACT_FINANCIALTRANSACTIONLINEITEM].[FINANCIALTRANSACTIONLINEITEMFACTID]

Definition

Copy


      CREATE view [BBDW].[v_FACT_RECURRINGGIFTINSTALLMENTWRITEOFF] as
        select
          rgiw.[RECURRINGGIFTINSTALLMENTWRITEOFFFACTID], 
          rgiw.[WRITEOFFDIMID], 
          rgiw.[WRITEOFFDATEDIMID], 
          rgiw.[WRITEOFFAMOUNT], 
          rgiw.[BASECURRENCYDIMID], 
          rgiw.[BASEWRITEOFFAMOUNT], 
          rgi.[RECURRINGGIFTINSTALLMENTFACTID], 
          rgi.[REVENUESCHEDULEDIMID], 
          rgi.[REVENUESCHEDULESTARTDATEDIMID], 
          rgi.[REVENUESCHEDULEENDDATEDIMID], 
          rgi.[RECURRINGGIFTINSTALLMENTDATEDIMID],     
          rgf.[RECURRINGGIFTFLAGDIMID],     
          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],
          r.[FINANCIALTRANSACTIONFACTID],
          r.[FINANCIALTRANSACTIONLINEITEMFACTID]
        from    
          BBDW.[FACT_RECURRINGGIFTINSTALLMENTWRITEOFF] rgiw
          left join BBDW.[FACT_RECURRINGGIFTINSTALLMENT] rgi
            on rgi.RECURRINGGIFTINSTALLMENTFACTID = rgiw.[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] r
            on rgi.[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]