Count the date between the range in hana?

[former_member383962]

Hi Experts,

I want to count the no.of.days between the range in an each partition.

s

create column table kabil_practice.days_btwn
(
id int,
s_date date
); 

insert into kabil_practice.days_btwn values ( 1,'20180201');
insert into kabil_practice.days_btwn values ( 1,'20180202');
insert into kabil_practice.days_btwn values ( 1,'20180101');
insert into kabil_practice.days_btwn values ( 2,'20180205');
insert into kabil_practice.days_btwn values ( 3,'20180206');

i tried this code:

select COUNT (*)  OVER (PARTITION BY id  ORDER BY s_date  
ROWS BETWEEN 10 PRECEDING AND CURRENT ROW)  CNT_OPEN  
from kabil_practice.days_btwn;

this what I got when I execute the above code.

But this is not correct one because it consider only the preceeding rows, i want to consider the range of values.

In SAP HANA RANGE is not supporting.

Is there is any alternative way to achieve the result....???

[SergioG_TX]

one way would be if you can create a new column on your table.. and calculate dates between date1 and date2.. there is a sql function days_between... the other alternative... I have seen ways to do a self referencing.. where you match row n to n+1... via a left join

hope this guides for your answer

[former_member383962]

Hi 9958e4b6df99431a84a41b015b639ac8 ,

Thanks for your reply....

Here, I can't Create a new column.. I Don't have Privilege to alter table....