OLAP function for rolling twelve month revenue

Database
Enthusiast

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_ID
CUSTOMER_ACCT_ID
CUSTOMER_ACCT_OPEN_DT
MONTH_END_DT
MONTH_REVENUE

Each 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
2 REPLIES
Enthusiast

Re: OLAP function for rolling twelve month revenue

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-dd

Insert Into Periods
Select
'$InDate' As InDate
, Add_Months((InDate (Date, Format 'yyyy-mm-dd')),-11) As Month_Start
, InDate
, (Month_Start/100*100)+1
, InDate
;

Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;
Insert Into Periods
Select
Add_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
, InDate
From Periods
Where Period_Dt = '$InDate'
;

Select * From Periods
order by 1 desc
;

-- Get the data

Select
InD.Store_Id
, Prd.Period_Dt
, Sum(InD.Month_Revenue)
From Tbl InD
Join Periods Prd
On InD.Month_End_Dt Between Prd.Month_Start_Dt And Prd.Month_End_Dt
And OnD.Customer_Acct_Open_Dt Between Prd.Cust_Acct_Open_St_Dt And Prd.Cust_Acct_Open_End_Dt
Group By 1,2
;
Teradata Employee

Re: OLAP function for rolling twelve month revenue

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)-1
GROUP BY STORE_ID , CUSTOMER_ACCT_ID , CUSTOMER_ACCT_OPEN_MNTH , MONTH_