Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-08-2015
03:29 PM

02-08-2015
03:29 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-09-2015
01:48 PM

02-09-2015
01:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

02-11-2015
04:45 PM

02-11-2015
04:45 PM

Re: Cumulative Sum by distinct customer ID by year

Thanks Dieter.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-05-2016
07:36 PM

04-05-2016
07:36 PM

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 :)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-06-2016
02:02 AM

04-06-2016
02:02 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-06-2016
02:04 AM

04-06-2016
02:04 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-06-2016
03:42 AM

04-06-2016
03:42 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-06-2016
11:21 AM

04-06-2016
11:21 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-06-2016
12:28 PM

04-06-2016
12:28 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

04-07-2016
12:43 AM

04-07-2016
12:43 AM

Re: Cumulative Sum by distinct customer ID by year

Exactly thought the same. Thanks ton for clarifying.

Regards,

Shavyani

Regards,

Shavyani