Calculation of date difference

[sudipDas]

Hi

I need help to write the SQL script in HANA AMDP.

I have a internal table with multiple materials with issue date. One material is having multiple lines with different issuing date. I need to calculate -

• Difference between the consecutive issue dates.
• Get the AVG of the difference

Giving the explanation below -

Regards

Sudip

[lbreddemann · Accepted solution]

Not sure why you want to do this with an AMDP. You can do this with native SQL.

Simply use WINDOWS functions (-> the documentation and the SCN search is your friend here) to access the preceding records.

- Lars

[sudipDas · Accepted solution]

Hi Lars

You are right , I am writing Native SQL codes in AMDP. Thanks for your pointers, I am looking for the Windows function in Native SQL.

[sudipDas · Accepted solution]

Hi Lars

I looked at the WINDOWS functions, It creates different partitions and do the calculations.

But I need to do subtraction between 2 issue date of the same materials.

Could you plz let me know how to write the below ABAP pseudo code in Native SQL.

***************************************************************

Internal table IT_TAB populated from a Select statement.

Loop at lt_tab into wa.

  if sy-index NE 1.

   if wa_copy-material EQ wa-material.

     lv_diff = wa_copy-issue_date - wa-issue_date .

     lv_add = lv_add + lv_diff.

  else.

    append wa_copy-material and lv_add.

  endif.

   endif.

  wa_copy = wa. 

endloop.

[lbreddemann · Accepted solution]

That's a "do my work for me" kind of request - so I won't do what you asked.

However, all you need to do is to partition by material, order the data by the issue date and access the "previous" row (LEAD/LAG window function might help here).

Try that and it shouldn't be too difficult. In case you actually get stuck, post the code you've managed to write so far and show us where you are stuck.

- Lars

[sudipDas · Accepted solution]

Hi Lars

I didn't ask you to do my work. Sorry for the confusion. I just gave my requirement to make it clear.

Anyway thanks for your help. I have done this with Lag Functions.