OLAP SQL - running count/total

Analytics

OLAP SQL - running count/total

Hi All,

We have the below data and using an SQL not procedural code, I would like to get the output as given below for 'out_count', 

it would help if we could do it using OLAP functions with Preceding and Following keywords, thank you.

cal_date, holiday_flag, out_count

'01-Aug-2016', 0,0

'02-Aug-2016', 0,0

'03-Aug-2016', 0,0

'04-Aug-2016', 0,0

'05-Aug-2016', 0,0

'06-Aug-2016', 1,1

'07-Aug-2016', 1,2

'08-Aug-2016', 1,3

'09-Aug-2016', 0,0

'10-Aug-2016', 0,0

'11-Aug-2016', 0,0

'12-Aug-2016', 1,1

'13-Aug-2016', 1,2

'14-Aug-2016', 1,3

'15-Aug-2016', 1,4

'16-Aug-2016', 0,0

create table dim_date

(cal_date date,

holiday_flag number(1));

insert into dim_date(cal_date, holiday_flag)

select '01-Aug-2016', 0 

union

select '02-Aug-2016', 0 

union

select '03-Aug-2016', 0 

union

select '04-Aug-2016', 0 

union

select '05-Aug-2016', 0 

union

select '06-Aug-2016', 1 

union

select '07-Aug-2016', 1 

union

select '08-Aug-2016', 1 

union

select '09-Aug-2016', 0 

union

select '10-Aug-2016', 0 

union

select '11-Aug-2016', 0 

union

select '12-Aug-2016', 1 

union

select '13-Aug-2016', 1 

union

select '14-Aug-2016', 1 

union

select '15-Aug-2016', 1 

union

select '16-Aug-2016', 0;

2 REPLIES
Teradata Employee

Re: OLAP SQL - running count/total

See RESET WHEN

Teradata Employee

Re: OLAP SQL - running count/total

i hope it will give you expected answer :)

SyntaxEditor Code Snippet

sel cal_date, holiday_flag,case when holiday_flag =0 then holiday_flag
else 
row_number () over (  order by cal_date
reset when holiday_flag  <> 
min(holiday_flag) over (order by cal_date
rows between 1 preceding and  1 preceding ) 
) end as out_count1
from dim_date