Skip to Content
-1

Count the date between the range in hana?

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....???

capture.png (1.3 kB)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Feb 10 at 02:33 PM
    -1

    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

    Add comment
    10|10000 characters needed characters exceeded