Breaking down time periods into percentages (per day)

[gdunn]

Hi,

I currently have a dashboard for a dataset of vending machine logs. I am currently working on identifying the percentage of downtime for all machines.

Here is an example of my current data table:

I need to figure out the percentage a machine has been offline (per device ID for each day).

I'm able to calculate the percentage per day when  a machine has been offline for a single day. The problem I'm having is that I'm not able to identify each and every day that a machine has been offline. I have a start date (when the machine first went offline) and an end date (when the machine came back online). Note: I have circled some examples above.

For example, if a machine went offline on 01/01/16 at 1.30PM and came back online 03/01/16 at 2.30PM. The breakdown needs to be as follows:

=======================================

Day 1 (01/01/16) - Machine offline at 1.30PM

=======================================

Offline for 13.5 Hours (56.25% Offline)

=======================================

Day 2 (02/02/16)

=======================================

Offline for 24 Hours (100% Offline)

=======================================

Day 3 (03/03/16) - Machine online at  2.30 PM

=======================================

Offline for 14.5 Hours (60.42% Offline)

I need a way to calculate the above. I would really appreciate it if someone could point me in the direction of a possible solution.

Thanks in advance.

Graham

[johnw · Accepted solution]

One approach would be to break all records at date borders. Maybe something like this:

[New Table]:

NOCONCATENATE

LOAD *

,interval([End Time]-[Start Time]) as [Duration]

;

LOAD

whatever fields you want to keep other than duration and start/end date/time

,time(if([Date]=[Start Date],[Start Time],0)) as [Start Time]

,time(if([Date]=[End Date],[End Time],1)) as [End Time]

;

LOAD *

,date([Start Date] + iterno() - 1) as [Date]

RESIDENT [Original Table]

WHILE [Start Date] + iterno() - 1 <= [End Date]

;

DROP TABLE [Original Table];

If you have any other numeric data on the original table that you want to be able to do accumulations on, though, this would break that data due to duplicates. You'd have to do something a little more complicated in that case, basically building a new table with only the date breakdowns, connected to the original by some unique ID associated with each original record.

[swuehl]

I think you should create a record for each date, i.e break your intervals spanning multiple dates up into subintervals.

Have a look at this blog post

Calculate hours between two Date/Time strings

from which I reused some code to create the subintervals:

Set TimestampFormat = 'M/D/YY hh:mm TT';
 
INPUT:
LOAD *, recno() as ID INLINE [
DT1, DT2
8/9/12 11:08 AM,8/9/12 2:57 PM
8/18/12 09:20 AM,8/20/12 01:13 PM
8/17/12 09:20 AM,8/20/12 01:13 PM
8/17/12 09:20 AM,8/19/12 01:13 PM
6/27/12 12:41 PM,    7/6/12 4:38 PM
6/29/12 4:45 PM,    7/6/12 4:19 PM    
8/1/12 09:00 AM, 8/3/12 10:00 AM
8/3/12 03:00 PM, 8/6/12 09:00 AM
];

TMP:
LOAD  ID,
Date(floor(DT1+iterno()-1)) as Date,
Time(if(iterno()=1, rangemin(rangemax(frac(DT1),maketime(0)),Dayend(0)), maketime(0))) as Start,
Time(if(daystart(DT1)+iterno()-1=daystart(DT2), rangemax(maketime(0),rangemin(frac(DT2),Dayend(0))),dayend(0))) as End
Resident INPUT
while daystart(DT2) >= daystart(DT1)+iterno()-1;