CODEWITHSUNDEEP
sqlteradata
pradeep panda
pradeep panda

Reputation: 65

Need sum of a column from a filter condition for each row

Need to get total sum of defect between main_date column and past 365 day (a year) from it, if any, for a single ID. And The value need to be populated for each row.

Have tried below queries and tried to use CSUM also but it's not working:

    1)  select sum(Defect) as "sum",Id,MAIN_DT  
      from check_diff 
    where MAIN_DT between  ADD_MONTHS(MAIN_DT,-12) and MAIN_DT group by 2,3;

    2)select    Defect,
    Type1,
    Type2,
    Id,
    MAIN_DT,
    ADD_MONTHS(TIM_MAIN_DT,-12) year_old,
    CSUM(Defect,MAIN_DT)
    from check_diff
    where
        MAIN_DT between ADD_MONTHS(MAIN_DT,-12) and MAIN_DT group by id;

The expected output is as below:

Defect     Type1    Type2     Id     main_dt    sum

1            a        a        1     3/10/2017  1
99           a        a        1     4/10/2018  99
0            a        b        1     7/26/2018  99
1            a        b        1     11/21/2018 100
1            a        c        2     12/20/2018 1

Upvotes: 0

Views: 43

Answers (1)

dnoeth
dnoeth

Reputation: 60482

Teradata doesn't support RANGE for Cumulative Sums, but you can rewrite it using a Correlated Scalar SUbquery:

select Defect, Id, MAIN_DT,
 ( select sum(Defect) as "sum"
   from check_diff as t2
   where t2.Id = t1.Id
     and t2.MAIN_DT > ADD_MONTHS(t1.MAIN_DT,-12) 
     and t2.MAIN_DT <= t1.MAIN_DT group by 2,3;
 ) as dt
from check_diff as t1

Performance might be bad depending on the overall number of rows and the number of rows per ID.

Upvotes: 1

Related Questions