Skip to Content

How to split date ranges to separate rows

Hi All,

I want to split date ranges to multiple rows of dates.

Source data:

Date_toDate_fromMaterial20-Aug-1622-Aug-1610019-Aug-1620-Aug-16200

I need to transform this to the below structure.

DateHeader 220-Aug-1610021-Aug-1610022-Aug-1610019-Aug-1620020-Aug-16200

Can you please suggest how to achieve this?


Thanks,

Shyam

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

2 Answers

  • Best Answer
    Posted on Aug 24, 2016 at 10:13 AM

    Why not just using what HANA provides out of the box. There is the table _SYS_BI.M_TIME_DIMENSION which can be filled with date information (Generate Time Data - SAP HANA Developer Guide for SAP HANA Studio - SAP Library).

    Then you can simply join your table against the standard table.

    The select

    select t2.date_sql, t1.material
    from <yourTable> as t1 inner join _sys_bi.m_time_dimension as t2
      on t1.date_from <= t2.date_sql
      and t1.date_to >= t2.date_sql;
    

    provides following output (not ordered):

    For information: I changed the names of the date_from/date_to columns for a better understanding (e.g. 20-Aug-16 = date_from, 22-Aug-16 = date_to).

    Regards,

    Florian


    tmp01.JPG (15.7 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Aug 24, 2016 at 07:41 AM

    A very similar question and solution to this is demonstrated in this post -

    Michael

    Add a comment
    10|10000 characters needed characters exceeded

    • Okay. If this is a real requirement, the maximum difference between the dates will be definable. The table can be populated using an automated mechanism (stored proc, ETL, etc.) and it only needs to be done once. It will also be small due to the datatypes and domain values. You could have populated it with 100 year's worth of data in a few minutes.

      However, if you are only working with a small dataset and performance is not a concern, a scripted solution using imperative constructs will achieve what you require.

      Michael

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.