Calculate week end date

Question

I am working on HANA and I am not able to figure it out how to get the expected output.

Let me throw some light on getting the output. I am working on a procedure and my job is to get the data inserted in that particular table which I am successfully inserting, But the data is not in a correct manner.

I have 3 columns in my output named: report_date, report_week, week_end.

If suppose I start my data from 2010-01-01 the week end should start from 2010-01-06. And In this way I want my data from 2010 till 2030. It should show every week start in report_date and every week end start in week_end. The role of report_week is to show which week is going on currently.

My error output:

I have my procedure which I am posting below:

    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_SERIES" 
    (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

Any help is really appreciated.

Answer 1

I think the problem is here:

week(add_days(generated_period_start , 2)) will return the week number within a year, so it will always be between 1 and 52. As week N will occur in 2010, 2011,...,2017 the max(report_date) will always be in the last year it occurs (so either 2016 or 2017).

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

So you have to extract the year as well from the report date and include it in the group by of the TT_WEEK_END select as well as in the join clause used for the INSERT statement.