Cumulative Sum by distinct customer ID by year

Database

Re: Cumulative Sum by distinct customer ID by year

Sorry for the confusion. I need to get distinct count of col5. Need to populate the aggregrate in decremental order.

 

1. 123 7/2/2014    VZ  2 -- here two is count which is distinct count(VZ,SP,VZ,SP)

2. 123 6/29/2014   SP  2 -- here two is count which is distinct count(SP,VZ,SP)

3. 123 6/24/2014   VZ  2-- here two is count which is distinct count(VZ,SP)

4. 123 6/23/2014   SP  1-- here one is the count which is distinct count(SP only) and there are no rows below.

 

I need to calculate the count col. like mentioned above

Junior Contributor

Re: Cumulative Sum by distinct customer ID by year

You need to nest OLAP functions, tag the first row with a new col5 value and then do a cumulative sum:

SELECT customer, col4, col5, 
SUM(x)
OVER (PARTITION BY customer
ORDER BY col4
ROWS UNBOUNDED PRECEDING)
FROM
(
SELECT customer, col4, col5,
CASE WHEN ROW_NUMBER() OVER (PARTITION BY customer, col5 ORDER BY col4) = 1 THEN 1 ELSE 0 END AS x
FROM tab
GROUP BY 1,2,3
) AS dt

Re: Cumulative Sum by distinct customer ID by year

Thanks Dieter. 

Enthusiast

Re: Cumulative Sum by distinct customer ID by year

Hello Folks,  

I have a requirement and should bring d data in below format

(col1) (Date1) (Date2) (Sum for days1-10.) (Sum days11-20) (sumdays21-30)    Total

A.        Xx.     Vvv.          532                                                                             532

B.      Cc          Cv.                                           453                                            453

c.       Ft.         As.                                                                     543.                543           

From d above example the date1 and date 2 are date Columns and it's being compared and difference is found as number of days . i.e. if date1>date 2 then date1-date2 else date2-date1 which would giv d number of days difference.

 

Now based on this number of days difference calculation the SUM is calculated group by on column 1 and will come under the respective days bucket, either (Sum for days1-10.)or (Sum days11-20) or  (sumdays21-30).

 

And the last column is the SUM of that specific row.

 

Hope I made my requirement clear, now how do I handle this at the Teradata query Level, Please help.

Thanks in Advance.

Best Regards,

Shavyani :)

Teradata Employee

Re: Cumulative Sum by distinct customer ID by year

Hi shavyani

I don't know what do you want to do exactly but I think this can help you


					
				
			
			
				
			
			
				
Teradata Employee

Re: Cumulative Sum by distinct customer ID by year

Hi shavyani

I don't know what do you want to do exactly but I think this can help you

create multiset volatile table MyDates
(
date1 date,
date2 date
)primary index( date1 )
on commit preserve rows;

insert into MyDates
select current_date+random(-15,15), current_date+random(-15,15)
from sys_calendar.calendar
sample 100;

select
  date1,
  date2,
  case when diferencia between 1 and 10 then diferencia else 0 end as Days_1_10,
  case when diferencia between 11 and 20 then diferencia else 0 end as Days_11_20,
  case when diferencia between 21 and 30 then diferencia else 0 end as Days_21_30,
  abs(date1-date2) as diferencia
from MyDates
where abs(date1-date2) between 1 and 30
Enthusiast

Re: Cumulative Sum by distinct customer ID by year

SEL 

CUST, DT1, DT2,

CASE   WHEN DIFF >=1 AND DIFF <=10 THEN DIFF ELSE 0 END AS SUM_1TO10,

CASE   WHEN DIFF >=11 AND DIFF <=20 THEN DIFF ELSE 0 END AS SUM_11TO20,

CASE   WHEN DIFF >=21 AND DIFF <=30 THEN DIFF ELSE 0 END AS SUM_21TO30,

CASE   WHEN DIFF <1 OR  DIFF >30 THEN DIFF ELSE 0 END AS SUM_DEF,

SUM_1TO10+SUM_11TO20+SUM_21TO30+SUM_DEF AS SUM_TOT FROM 

(SEL   CUST, DT1, DT2,

SUM(

CASE 

WHEN DT1 > DT2 THEN DT1-DT2

WHEN DT2 > DT1 THEN DT2-DT1

ELSE 0

END) OVER (PARTITION BY CUST ORDER BY 1 ) AS DIFF

FROM 

(

SEL * FROM   (SEL 'A' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-11' AS DT2) A

UNION ALL

SEL * FROM   (SEL 'B' AS CUST, DATE '2001-01-21' AS DT1, DATE '2001-01-01' AS DT2) B

UNION ALL

SEL * FROM   (SEL   'C' AS CUST, DATE '2001-01-01' AS DT1, DATE '2001-01-31' AS DT2) C

UNION ALL

SEL * FROM   (SEL 'A' AS CUST, DATE '2001-02-21' AS DT1, DATE '2001-01-11' AS DT2) A

) A

) A

Enthusiast

Re: Cumulative Sum by distinct customer ID by year

Thanks ton Atar and  Yuvae I shall implement this and get back.

Yuvae a small clarfication I would like to have , here is the union all clauses is being done based on the Cust value but if der are 1000 Cust values we wouldn't b able to write as many Union All, So is there an alternate to this Union All loop.

Please Advice.

Thanks in Advance :)

Best Regards,

Shavyani

Junior Contributor

Re: Cumulative Sum by distinct customer ID by year

The UNION is just for creating the base data, of course this will be a Select on your actual table.

Enthusiast

Re: Cumulative Sum by distinct customer ID by year

Exactly thought the same. Thanks ton for clarifying.

Regards,
Shavyani