Populate values between start date and end date

[Dayna]

Hello All!

I have a small problem with one of my reports, rather than using IntervalMatch I need to populate the values between the start and end for each record. I have attached an example report to show the set of data I'm currently using.

Ideally, I'd like the complete hours populated between the start_timestamp and end_timestamp, i.e.

This is my record at the moment:

id	start_timestamp	end_timestamp
5	29/03/2017 15:53	30/03/2017 07:19

And I'd like to show:

id	start_timestamp	timestamp	end_timestamp
5	29/03/2017 15:53	29/03/2017 15:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 16:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 17:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 18:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 19:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 20:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 21:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 22:00	30/03/2017 07:19
5	29/03/2017 15:53	29/03/2017 23:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 00:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 01:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 02:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 03:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 04:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 05:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 06:00	30/03/2017 07:19
5	29/03/2017 15:53	30/03/2017 07:00	30/03/2017 07:19

Can someone help me, please?

[tresesco]

May be like:

=Interval(Timestamp#( end_timestamp,'DD/MM/YYY hh:mm')-Timestamp#( start_timestamp,'DD/MM/YYY hh:mm'),'hh:mm:ss')

[Dayna]

That only seems to give a value between those dates, rather than populating missing values? Isn't it more like a cartesian timeframe?

[tresesco · Accepted solution]

Well, may be this then ?

Load

  *,

  Timestamp(Floor(start_timestamp, 1/24)+IterNo()*1/24) as timestamp

While Floor(start_timestamp, 1/24)+(IterNo()-1)*1/24 <=end_timestamp;

load

  Timestamp#(start_timestamp,'DD/MM/YYY hh:mm') as start_timestamp,

  Timestamp#(end_timestamp,'DD/MM/YYY hh:mm') as end_timestamp

Inline [

id, start_timestamp, end_timestamp

5, 29/03/2017 15:53, 30/03/2017 07:19]

[Dayna]

You're an absolute star.. thank you!