Generate 1 row per hour when id stay enable

[Anonymous]

i have a problem in my table... I have datetime of creation and datetime of disable, for example:

ID 1

Datetime of Creation: 2016-01-20 06:21:12

Datetime of Disable: 2016-01-20 22:45:10

In this case will be generate 17 rows, one per hour for every hour the ID 1 stay enable, it's possible to utilising the function IterNo() in this case or another solution?

(I open another discussion because my reply get in moderated post)

[maxgro]

try

I:

load

  timestamp(Timestamp#(start, 'YYYY-MM-DD hh:mm:ss')) as start,

  timestamp(Timestamp#(end, 'YYYY-MM-DD hh:mm:ss'))  as end

inline [

start, end

2016-01-20 06:21:12, 2016-01-20 22:45:10

];

load start, end, timestamp(floor(start + iterno()/24, 1/24))

Resident I

while start + (IterNo()-1)/24 <= end;

[Anonymous]

Thanks, but using the floor function, the first row would be 06:00 instead 07:00 ?

[Anonymous]

You have a start time and an end time, so the IntervalMatch() function could well help you.  It is explained in this blog post, IntervalMatch

[maxgro]

start + iterno()/24 in the while loop is like start + 1 hour, start + 2 hour, .....

floor(................., 1/24) will floor to 00 min

[MarcoWedel]

Hi,

one example could be:

table1:
LOAD *,
    AutoNumberHash128([Datetime of Creation],Ceil([Datetime of Disable],'01:00')) as EnableIntervID;
LOAD *,
    Timestamp([Datetime of Creation]+fabs(NORMINV(Rand(),0,1))) as [Datetime of Disable];
LOAD RecNo() as ID,
    Timestamp(Today()-Rand()*100) as [Datetime of Creation]
AutoGenerate 1000;

tabCalendar: 
LOAD *,
    Day(DateTime) as Day, 
    DayName(DateTime) as Date,
    WeekDay(DateTime) as WeekDay, 
    Week(DateTime) as Week, 
    WeekName(DateTime) as WeekName, 
    Month(DateTime) as Month, 
    MonthName(DateTime) as MonthName, 
    Dual('Q'&Ceil(Month(DateTime)/3),Ceil(Month(DateTime)/3)) as Quarter, 
    QuarterName(DateTime) as QuarterName, 
    Year(DateTime) as Year, 
    WeekYear(DateTime) as WeekYear;   
LOAD Timestamp(Floor(MinDateTime,'01:00')+(IterNo()-1)*'01:00') as DateTime 
While Floor(MinDateTime,'01:00')+(IterNo()-1)*'01:00' <= Ceil(MaxDateTime,'01:00'); 
LOAD Min([Datetime of Creation]) as MinDateTime, 
    Max([Datetime of Disable]) as MaxDateTime 
Resident table1;

tabLink:
IntervalMatch(DateTime)
LOAD Distinct
    [Datetime of Creation],
    Ceil([Datetime of Disable],'01:00') as [Datetime of Disable]
Resident table1;

Left Join (tabLink)
LOAD Distinct
    [Datetime of Creation],
    [Datetime of Disable],
    AutoNumberHash128([Datetime of Creation],[Datetime of Disable]) as EnableIntervID
Resident tabLink;

DROP Fields [Datetime of Creation],[Datetime of Disable] From tabLink;

hope this helps

regards

Marco