# BFL Function #1 : working with DAYS function

Hello Everyone,

here I am demonstrating the BFL function DAYS

to Calculate number of days between every Sales Order date and Current System date

My Database table “INTERNETSALES” contains approximately 60,000 records

I have a Date Column “ORDERDATEKEY

–Regular SQL logic

SELECT “ORDERDATEKEY” AS “FROMDATE”,

CURRENT_DATE   AS “TODATE”,

DAYS_BETWEEN( “ORDERDATEKEY”, CURRENT_DATE ) AS “TOTALDAYS”

FROM “HANAGA”.“INTERNETSALES”;

With regular SQL I got the result in 15ms

Now, let us see how much time it takes if it is implemented with BFL functions “DAYS

Here I created a Custom Procedure that executes Procedure related to BFL function “DAYS

–BFL Function “DAYS”

–to find number of days between every Sales date of table INTERNETSALES

–and Current date of the System and

–to combine the result of DAYS functions with Sales date column

DROP PROCEDURE   “HANAGA”.“BFL_DAYS_EXAMPLE”;

CREATE PROCEDURE “HANAGA”.“BFL_DAYS_EXAMPLE”

LANGUAGE SQLSCRIPT

AS

BEGIN

tab1 = CE_COLUMN_TABLE( “HANAGA”.“INTERNETSALES” );

tab2 = CE_PROJECTION(

:tab1,

[“ORDERDATEKEY”,

CE_CALC( ‘ leftstr(string(“ORDERDATEKEY”),4) + midstr(string(“ORDERDATEKEY”),6,2) + midstr(string(“ORDERDATEKEY”),9,2) ‘, VARCHAR(8) ) AS “FROMDATE”]

);

tab3 = CE_PROJECTION(

:tab1,

[ “ORDERDATEKEY”,

CE_CALC( ‘ leftstr(string(now()),4) + midstr(string(now()),6,2) + midstr(string(now()),9,2) ‘, varchar(8) ) AS “TODATE”]

);

tab4 = CE_PROJECTION(

:tab1,

[ “ORDERDATEKEY”,

CE_CALC( ‘ 0 ‘, DOUBLE ) AS “CONFIG”

]

);

fromdate_tab =  CE_PROJECTION(

:tab2,

[ “FROMDATE” ]

);

todate_tab =  SELECT “TODATE” FROM :tab3;

config_tab =  SELECT “CONFIG” FROM :tab4;

CALL “_SYS_AFL”.“AFLBFL_DAYS_PROC”(

:fromdate_tab,

:todate_tab,

:config_tab,

:out_tab

);

o_tab = CE_VERTICAL_UNION(  :fromdate_tab, [“FROMDATE”],

:todate_tab, [“TODATE”],

:out_tab, [“TOTALDAYS”]

);

SELECT * FROM :o_tab;

END;

–Executing above procedure

CALL “HANAGA”.“BFL_DAYS_EXAMPLE”;

With BFL function and pure SQLScript I expected quick result. But it has taken more time.

But, this observation has given me good knowledge on CE_CALC and CE_PROJECTION functions

Enjoy HANA

Regards

Nagababu Tubati

### Assigned Tags

1 Comment
You must be Logged on to comment or reply to a post.

Very Good example , will try to implement in my System & expecting more examples on PAL & BFL if you can write ðŸ™‚ .. Thanks Shivam