How to split date ranges to separate rows

[shyam_uthaman]

Hi All,

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

Source data:

Date_to	Date_from	Material
20-Aug-16	22-Aug-16	100
19-Aug-16	20-Aug-16	200

I need to transform this to the below structure.

Date	Header 2
20-Aug-16	100
21-Aug-16	100
22-Aug-16	100
19-Aug-16	200
20-Aug-16	200

Can you please suggest how to achieve this?

Thanks,

Shyam

[pfefferf · Accepted solution]

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

[shyam_uthaman · Accepted solution]

Perfect! Almost forgot about that table.

Thanks Florian & Michael.

[michael_eaton3 · Accepted solution]

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

[michael_eaton3]

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

Michael

[shyam_uthaman]

Thanks Michael.

Not sure if the same solution can split up dates.

In that case, there was a separate master data table with the split logic.

Again ,maybe I've understood it well enough though.

[michael_eaton3]

You should re-read it, its very similar.

You will calculate the number of days in your range (rather than weeks), then join to day multiplier table (rather than week multiplier).

Your requirement is simpler as you don't need to allocate data across the rows.

Michael

[shyam_uthaman]

Hi Michael,

The maximum difference between Date_from and Date_to in practical scenarios is not defined in my case. So, in this case, if the max difference is 200 days, I would need quite a number of entries in my custom table.

But again, I don't know the max difference and that makes it difficult since I may not be able to maintain this custom table for all possible values.

Regards,

Shyam

[michael_eaton3]

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