How to create a "history" table in calaculation view for each day based on "time dimension"

Question

I have very HANA/SQLScript specified problem. I need to create a history view for each day in form of scripted calculation view based on table with dates gaps?

I tried every cross joins with "_SYS_BI"."M_TIME_DIMENSION" and just use Functions.

In my experience cross joins are ideal fro cumulative sum but not for showing single value.

HANA dont allow to use columns as a input parameters to table functions.

Scalar functions cannot be used in calculation view too. I can activate that view but i cannot read data from it. Even if function standalone works fine:

Business Partner Discount Table:

 CREATE COLUMN TABLE "BP_DISCOUNT" ("BP_CODE" VARCHAR(50) NOT NULL ,
         "DATE" DATE L ,
         "DISCOUNT" DECIMAL(21,6);


insert into "BP_DISCOUNT" values('abc','20190101','0');
insert into "BP_DISCOUNT" values('abc','20190105','5');
insert into "BP_DISCOUNT" values('abc','20190110','10');

My function that i wanted to use in place of:

CREATE FUNCTION bp_discountF (BP_Code varchar(50), Date_D date)
 RETURNS discount decimal(21,6)
 LANGUAGE SQLSCRIPT 
 SQL SECURITY INVOKER 
 AS
 BEGIN

 select  "DISCOUNT" into DISCOUNT
from "BP_DISCOUNT" d
where 
:BP_CODE = d."BP_CODE" and d."DATE"<= :DATE_D order by d."DATE" desc limit 1;
 END;

My goal is to create a view that shows discount values for every possible day based on most recent value. It must be in form of scripted calculation view to join it to more complex sales report views.

Expected result is to join it on BP and document date:

...
'abc', '20190101', 0;
'abc', '20190102', 0;
'abc', '20190103', 0;
'abc', '20190104', 0;
'abc', '20190105', 5;
'abc', '20190106', 5;
'abc', '20190107', 5;
'abc', '20190108', 5;
'abc', '20190109', 5;
'abc', '20190110', 10;
'abc', '20190111', 10;
..

score 0 · Answer 1 · answered Oct 07 '19 at 13:41

you could try it like this:

DO BEGIN

times=
select DATE_SAP from M_TIME_DIMENSION
where DATE_SAP between '20190101' and '20190110';

dates=
select * from :times
left join bp_discount
on DATE <= DATE_SAP
order by DATE_SAP;

listdates=
select DATE_SAP, BP_CODE, max(DATE) as DATE
from :dates
group by DATE_SAP, BP_CODE
order by DATE_SAP;

select ld.DATE, ld.DATE_SAP, ld.BP_CODE, bpd.DISCOUNT from :listdates as ld
inner join bp_discount as bpd
on ld.BP_CODE = bpd.BP_CODE and ld.DATE = bpd.DATE
order by DATE_SAP;

END;

Times are just the dates you need, to make it easier I just selected to needed ones..

In dates, you get a table of every date from your discount table, and every date from the Time Dimension Table that is bigger than that.

Now you want every time the max Date, because of course, 06.01.2019 is also bigger than 01.01.2019, but you want the reference to the 05.01.2019 at this date. So you select Max(DATE) and you get the 01.01.2019 for every Day before the 05.01.2019 which is the next day in your pb_discount list.

Since you cannot group by the the discount, you join that as a last step and should have the table you need.

How to create a "history" table in calaculation view for each day based on "time dimension"

1 Answers1