OLAP SQL - running count/total

Database

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;

1 REPLY
Junior Supporter

Re: OLAP SQL - running count/total

Below is the solution and answer is inspired from 

Dieter Answer  from the link below


select cal_date, 

  holiday_flag, 

 sum(holiday_flag) over(order by cal_date  RESET WHEN holiday_flag =  0 rows unbounded preceding )

as count_num from dim_date