Output not coming as expected

[Former Member]

error.png - This is the output which I am getting after running the procedure.

expected.png - This is the kind of output I am looking for. Just that It should start from 2010-01-01 instead of 2017.

Below is my current code:-

create procedure bhavya.zz_get_series()

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

--DEFAULT SCHEMA <default_schema_name>

AS

LV_START_DATE date := '2010-01-01' ;

LV_END_DATE date := current_date ;

LV_WEEK_END NVARCHAR(10) := 'FRIDAY';

lv_report_date Date ;

Begin TT_SERIES = (SELECT GENERATED_PERIOD_START AS REPORT_DATE, week(add_days(generated_period_start , 2)) as report_week, current_date as week_end --added ABHOOT FROM SERIES_GENERATE_DATE ( 'INTERVAL 1 DAY', :LV_START_DATE, ADD_DAYS(coalesce(current_date,:LV_END_DATE), 1)));

TT_WEEK_END = select report_week, max(report_date) as week_end from :TT_SERIES group by report_week ;

insert into "BHAVYA"."AFS_BASE.KPI.TABLES::DB_WEEK_SERIES1" (REPORT_DATE,REPORT_WEEK,WEEK_END) select S.report_date, w.report_week, w.week_end from :TT_SERIES S left join :TT_WEEK_END W on w.report_week = s.report_week;

end;

call bhavya.zz_get_series

Tried doing different stuff but could not find how to do it. Any help is appreciated. Thanks.

[Former Member]

The difference between my result and the expected result is:-

In my result, the first column is report_date and it's starting date is 2010-01-01 which is perfectly fine. The problem is in end_date column which shows 2016-12-30. The end date value is wrong. It should show 2010-01-06 instead of 2016-12-30 because what I am trying to do here is taking every week start and taking every week end starting from 2010 till 2030.

I tried myself different things but nothing work out. Any help is really appreciated. Thanks.

[Mushfiq]

where will I get SERIES_GENERATE_DATE ?

[Former Member]

Hi Abdul,

SERIES_GENERATE_DATE is already there in my system and it's working properly. I do not think this is the problem here.