Calculate Running Ave

General
Enthusiast

Calculate Running Ave

Hi there, I'm trying to calculate the running ave, I have catered that it will start fresh evey month meaning day1-1, day1-2, day1-3, day1-4 ect, problem that I have is when an account is only opended on say the 15th of each month the ave must only start to calc when the account was active meaning balance changed from 0 to . that 0 or < that 0, any ideas rather than my code below:

(

case

when month(DDate) in (1, 3, 5, 7, 8, 10, 12)

then avg(Balance) over(partition by Acc, month(DDate)

order by Acc, DDate rows between 31 preceding and current row)

when month(DDate) in (4, 6, 9, 11)

then

avg(Balance)

over(partition by Acc, month(DDate)

order by Acc, DDate rows between 30 preceding and current row)

--Leap Yeat Calculation

when (month(DDate) in (2) and year(DDate) MOD 4 = 0)

then AVG(Balance)

over(partition by Acc, month(DDate)

order by Acc, DDate rows between 29 preceding and current row)

else

AVG(Balance)

over(partition by Acc, month(DDate)

order by Acc, DDate rows between 28 preceding and current row)

end

)

as Ave_Daily_Balance

4 REPLIES
Senior Apprentice

Re: Calculate Running Ave

You already got the month in PARTITION BY, so it already starts with the 1st of month, no additional logic needed. Plus there's no MONTH function in Teradata SQL (this is ODBC syntax)

AVG(Balance)
over(partition by Acc, extract(year from DDate), extract(month from Date)
order by DDate
rows unbounded preceding)
Enthusiast

Re: Calculate Running Ave

Thanks Dieter, but I need to only cal the average balance from where the account was active meaning the balance changed from 0 to whatever amount, if the account was active from 4th then the ave calc must start on the 4th to month end.

Acc    DDate         Balance

1234 2015-04-01 0.00

1234 2015-04-02 0.00

1234 2015-04-03 0.00

1234 2015-04-04 20000.00

1234 2015-04-05 20000.00

1234 2015-04-06 21390.00

Senior Apprentice

Re: Calculate Running Ave

My query returns exactly the same result as yours :-)

How do you define an "active" account?

To simply exclude zero amounts you might do AVG(NULLIF(Balance,0)), but this will also exclude later rows when the amounts happens to be zero. 

Enthusiast

Re: Calculate Running Ave

Perfect thanks Dieter, that is what I need.