All Forums General
zarina 1 post Joined 07/16
03 Jul 2016
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

 

12 Jul 2016

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

 

You must sign in to leave a comment.