Skip to Content
author's profile photo Shyam Uthaman
0
Shyam Uthaman

How to split date ranges to separate rows

Posted on Aug 24, 2016 at 07:31 AM 914 Views

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
    author's profile photo Florian Pfeffer
    Florian Pfeffer
    Posted on Aug 24, 2016 at 10:13 AM
    4

    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

    • Michael Eaton
      Aug 24, 2016 at 10:43 AM

      It's almost the same solution as a master data table is required. 😉

      However, I prefer to use an equi join as I have faith (and experience) that this will perform much better than a theta join in HANA (and all other RDBMSs). Although this is only relevant if performance is important.

      Michael

    Comment on This Answer
  • author's profile photo Michael Eaton
    Michael Eaton
    Posted on Aug 24, 2016 at 07:41 AM
    0

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

    Michael

    Add a comment
    10|10000 characters needed characters exceeded

    • Michael Eaton Shyam Uthaman
      Aug 24, 2016 at 09:02 AM

      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

    Comment on This Answer

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.