cumulative sum and moving average

General
N/A

cumulative sum and moving average

Hi All

 

I am new to teradata and looking for the teradata query for the below situation

query to retireve cumulative amount and moving average salary for each employee in the reporting period of the first 6 months of 2015

emp_id cont_join_date cont_end_date salary
4 2014-10-08 2014-12-08 300
4 2014-12-09 2015-01-04 2650
4 2015-01-05 2015-01-31 2580
4 2015-02-01 2015-03-03 2680
4 2015-03-04 9999-12-31 3876
5 2015-03-21 2015-04-02 5382
5 2015-04-03 2015-05-08 4330
5 2015-05-09 2015-05-18 5877
3 2015-05-15 2015-05-31 0
5 2015-05-19 2015-06-11 3570
3 2015-06-01 2015-06-30 6000
5 2015-06-12 2015-07-31 4500
3 2015-07-01 9999-12-31 5090
5 2015-09-01 9999-12-31 2508
1 REPLY
Enthusiast

Re: cumulative sum and moving average

You can try something like below

SEL CSUM(salary,cont_join_date), MAVG(salary,6,cont_join_date)
FROM <TABLENAME>
WHERE EXTRACT (YEAR FROM cont_join_date) = 2015
AND EXTRACT (MONTH FROM cont_join_date) BETWEEN 1 AND 6
GROUP BY EMP_ID