Not getting the output as expected

Question

I am working on SAP 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:- enter image description here

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.

polyglot_phil polyglot_phil · Accepted Answer · 2017-07-18T21:10:18

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.

Not getting the output as expected

1 Answers