All Forums Database
Nolan Madson 12 posts Joined 11/08
13 Sep 2009
OLAP function for rolling twelve month revenue

I know this should be a relatively simple application of an OLAP function, but I've been working on this problem so long (and am facing a Tuesday deadline) my brain is mush. The only way I can see to do this is to write a procedure for each month in my 12 month window. Somehow having two dates, CUSTOMER_ACCT_OPEN_DT and MONTH_END_DT, is bollixing any solution I try.My data: STORE_IDCUSTOMER_ACCT_IDCUSTOMER_ACCT_OPEN_DTMONTH_END _DTMONTH_REVENUEEach month I want to recalculate the prior 12 months' data since the store receipts may be adjusted retroactively. I want to report on SUM(MONTH_REVENUE) for the prior 12 months by store for those customers who have opened accounts in the prior twelve months.So each month I want 12 results per STORE_ID with results aggregated over a 12 month window.In August, run for July with input variable of '2009-07-31' July, 2009 - sum of MONTH_REVENUE for customers with CUSTOMER_ACCT_OPEN_DT BETWEEN '2008-08-01' and '2009-07-31' and MONTH_END_DT between '2008-08-31' and '2009-07-31'Jun, 2009 - sum of MONTH_REVENUE for customers with CUSTOMER_ACCT_OPEN_DT BETWEEN '2008-07-01' and '2009-06-30' and MONTH_END_DT between '2008-07-31' and '2009-06-30'...Aug, 2008 - sum of MONTH_REVENUE for customers with CUSTOMER_ACCT_OPEN_DT BETWEEN '2007-09-01' and '2008-08-31' and MONTH_END_DT between '2007-09-30' and '2008-08-31'In September I'll drop the most recent 11 months' data (since we assume that receipts data becomes stable after a year.)In Sep, run for Aug with input variable of '2009-08-31'Aug, 2009 - sum of MONTH_REVENUE for customers with CUSTOMER_ACCT_OPEN_DT between '2008-09-01' and '2009-08-31' and MONTH_END_DT between '2008-09-30' and '2009-08-31'...Sep, 2008 - sum of MONTH_REVENUE for customers with CUSTOMER_ACCT_OPN_DT between '2007-10-01' and '2008-09-30' and MONTH_END_DT between '2007-10-31' and '2008-09-31'Thanks in advance for any assistance anyone might be able to provide,Nolan

Jimm 298 posts Joined 09/07
14 Sep 2009

I can see why OLAP is giving you problems.Try the enclosed - build a refernece table of required dates and do a product join to this.Create Volatile Table Periods(Period_Dt Date Not Null, Month_Start_Dt Date Not Null, Month_End_Dt Date Not Null, Cust_Acc_Open_St_Dt Date Not Null, Cust_Acc_Open_End_Dt Date Not Null )Unique Primary Index (Period_Dt)On Commit Preserve Rows;-- Set up the twelve months period detail-- Your variable is in $InDate as yyyy-mm-ddInsert Into PeriodsSelect '$InDate' As InDate, Add_Months((InDate (Date, Format 'yyyy-mm-dd')),-11) As Month_Start, InDate, (Month_Start/100*100)+1, InDate;Insert Into PeriodsSelectAdd_Months(Period_Dt,-1) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-2) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-3) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-4) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-5) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-6) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-7) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-8) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-9) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-10) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Insert Into PeriodsSelectAdd_Months(Period_Dt,-11) As InDate, Add_Months((InDate+1 (Date, Format 'yyyy-mm-dd')),-11)-1 As Month_Start, InDate, (Month_Start/100*100)+1, InDateFrom PeriodsWhere Period_Dt = '$InDate';Select * From Periodsorder by 1 desc;-- Get the dataSelect InD.Store_Id, Prd.Period_Dt, Sum(InD.Month_Revenue)From Tbl InDJoin Periods PrdOn InD.Month_End_Dt Between Prd.Month_Start_Dt And Prd.Month_End_DtAnd OnD.Customer_Acct_Open_Dt Between Prd.Cust_Acct_Open_St_Dt And Prd.Cust_Acct_Open_End_DtGroup By 1,2;

Vador 36 posts Joined 08/07
14 Sep 2009

If I did understand correctly what's your need, I think that its possible to have it by this query :SELECT STORE_ID, CUSTOMER_ACCT_ID, (((CUSTOMER_ACCT_OPEN_DT ( DATE , FORMAT 'YYYY-MM'))(CHAR(7)))(DATE,FORMAT 'YYYY-MM')) AS CUSTOMER_ACCT_OPEN_MNTH, (((MONTH_END_DT ( DATE , FORMAT 'YYYY-MM'))(CHAR(7)))(DATE,FORMAT 'YYYY-MM')) AS MONTH_, SUM( SUM(MONTH_REVENUE) ) OVER( PARTITION BY STORE_ID , CUSTOMER_ACCT_ID , CUSTOMER_ACCT_OPEN_MNTH ROWS BETWEEN 11 PRECEDING AND CURRENT ROW ORDER BY CUSTOMER_ACCT_OPEN_MNTH DESC )FROM WHERE -- Filtering rows in order to keep rows which are whithin the 12 previous months regarding MONTH_END_DT CUSTOMER_ACCT_OPEN_DT BETWEEN ADD_MONTHS( MONTH_ , -11 ) AND ADD_MONTHS( MONTH_,+1)-1GROUP BY STORE_ID , CUSTOMER_ACCT_ID , CUSTOMER_ACCT_OPEN_MNTH , MONTH_

You must sign in to leave a comment.