splitTimeSpans.txt

SUB SplitTimeSpans (parTable, parFromField, parToField, parResolution, parFormat, parCutDateField, parCutFormat, parDurationField, parFillTo, parCheckSums)

	TRACE Calling sub "SplitTimeSpans" by Christof Schwarz;
    SET subCheckSumTolerance = 0.00001; // acceptable rounding diffences between CheckSum1 and CheckSum2
    
// Sub splits time-spans into multiple rows, when the span crosses a time-cut. The time-cut limits are defined by a
// Resolution parameter :
// 
// parTable (obligatory)      : name of the table where your From and To dates are found
// parFromField (obligatory)  : name of the From date field
// parToField (obligatory)    : name of the To date field
// parResolution (obligatory) : how many multiples of days do you want splitting time-cuts (1 = 1 per day,
//                              1/24 = 1 per hour)
// parFormat (obligatory)     : TimestampFormat to parse and render the newly generated From and To timestamp fields 
//                              e.g. 'YYYY-MM-DD hh:mm:ss'
// parCutDateField (optional) : This field will hold the closest cut-date for the given period, this is good
//                              to link it to your MasterCalendar. If parameter is ommited, the field will
//                              temporarily be created but removed before the Sub finishes
// parCutFormat (obligatory)  : Date or TimestampFormat for the newly generated "putCutDateField"  
//                              e.g. 'YYYY-MM-DD', can also be 'Num' to leave it as a numeric, not a timestamp/date value
// parDurationField (optional): leaves a Duration field of the result rows, so you can easily do Sum(Duration)
//                              in your frontend. If parameter is ommited, the duration field will
//                              temporarily be created but removed before the Sub finishes. It does so to 
//                              compare the initial Sum(~Duration) with the final Sum(~Duration) and will
//                              break script execution in case they don't match (logic failed, shouldn't happen)
// parFillTo (optional)       : put 'Now()' (as string) if you want to fill missing To-Date/times up to Now(). If no
//                              fill value or 'Null()' is given, no filling of missing To-values is made
// parCheckSums (optional)    : set to 0 to not compare the Sum of Duration before and after the operation (faster
//                              script execution), default is 1
// Examples:
// CALL SplitTimeSpans ('My Intervals', 'FROM DATE', 'TO DATE', 1, 'YYYY-MM-DD hh:mm:ss','%CALDATE', 'YYYY-MM-DD', 'DURATION', 'Now()' 0);
// CALL SplitTimeSpans ('My Intervals', 'FROM DATE', 'TO DATE', 1, 'YYYY-MM-DD hh:mm:ss','%CALDATE', 'Num', 'DURATION', 'Now()', 0);
// CALL SplitTimeSpans ('tFACTS', 'FromDateTime', 'ToDateTime', 1/24, 'DD.MM.YYYY hh:mm:ss', Null(), Null(), 'Duration', Null(), 1);
// CALL SplitTimeSpans ('Facts', 'Facts.From', 'Facts.To', 0.5, 'MM/DD/YYYY hh:mm:ss');
//
// The resulting table has more rows than the original (provided that some durations spun across multiple time cuts).
// It has the same fields names as before but we added some columns and manipulated two:
// manipulated fields:
//  - the field <parFromField> after the operation can be either the original or the closest cut-time
//  - the field <parToField> after the operation can be either the original or the closest cut-time
// new fields:
//  - the original <parFromField> value is still kept in field <parFromField>.Original
//  - the original <parToField> value is still kept in field <parToField>.Original
//  - a field <parFromField>.IsOrig is added, which is 1 for the row that still shows the original <parFromField>, 0 for rows
//    which were created do to splitting of long periods
//  - the <parCutFormat> is added in the format <parCutFormat> to show the corresponding closest time cut in the past  
 
    IF Len('$(parTable)')*Len('$(parFromField)')*Len('$(parToField)')*Len('$(parResolution)')*Len('$(parFormat)') = 0 THEN
       [Error: you did not provide all necessary parameters to sub SplitTimeSpans(...)];
    END IF
    
	// set default values for optional parameters
	LET subDurationField = If(Len('$(parDurationField)'), '$(parDurationField)', '$(parTable)~Duration');
    LET subCutDateField = If(Len('$(parCutDateField)'), '$(parCutDateField)', '$(parTable)~CutDate');
    LET subCutFormat = If(Len('$(parCutFormat)'), '$(parCutFormat)', '$(parFormat)');
    LET subResolution = Num(parResolution , '','.',' ');  // Format parameter to US num format
    LET subRowsBefore = NoOfRows('$(parTable)');
    LET subCheckSums = Alt('$(parCheckSums)', 1);
    LET subFormatFunct = If(parCutFormat LIKE 'NUM', 'Num', 'TimeStamp');
    LET subFormatParam = If(parCutFormat LIKE 'NUM', '', ',''$(subFormatParam)'' ');
    LET subFillTo = If(Len('$(parFillTo)'), parFillTo, 'Null()');
    
    // Get all existing fields of the table into a comma-separated list in "subLoadBlock". If the fields
    // are the From- and To-fields as per the parameters, load them into fieldname + ".Original" 
    LET subLoadBlock = '';
    FOR subV = 1 TO NoOfFields(parTable)
        LET subField = FieldName(subV, parTable);
        IF Match(subField, parFromField, parToField) THEN
    		LET subLoadBlock = subLoadBlock & If(Len(subLoadBlock),', ') & '[$(subField)] AS [$(subField).Original]';
        ELSE
            LET subLoadBlock = subLoadBlock & If(Len(subLoadBlock),', ') & '[$(subField)]';
        ENDIF
    NEXT subV;
    
    // Phase 1) add the Floor of FROM and TO
    TRACE SUB SplitTimeSpans - Phase 1;
    [$(parTable)~1]:
    LOAD 
        $(subLoadBlock),
        [$(parFromField)],
        Alt([$(parToField)], TimeStamp($(parFillTo),'$(parFormat)')) AS [$(parToField)],
        Alt([$(parToField)], TimeStamp($(parFillTo),'$(parFormat)')) - [$(parFromField)] AS [$(subDurationField)],
        Floor([$(parFromField)], $(subResolution)) AS [$(parFromField).Floor], 
        Floor(Alt([$(parToField)], TimeStamp($(parFillTo),'$(parFormat)')), $(subResolution)) AS [$(parToField).Floor]
    RESIDENT    
        [$(parTable)];       
   
    IF subCheckSums THEN
        TRACE Creating Checksum (1)...;
        [~CheckSums1]: 
        LOAD 
        	Sum([$(subDurationField)]) AS [~CheckSum1] 
        RESIDENT [$(parTable)~1];
        LET vCheckSum1 = Peek('~CheckSum1',0,'~CheckSums1');
        TRACE CheckSum1 Duration = $(vCheckSum1);
        DROP TABLE [~CheckSums1];
    END IF
    DROP FIELD [$(subDurationField)] FROM [$(parTable)~1];
    DROP TABLE [$(parTable)];
    
    // Phase 2) Find min and max date in fields FROM and TO
    TRACE SUB SplitTimeSpans - Phase 2;
    TRACE Looking for min date of field "$(parFromField)";    
    [~tmpMinDate]:
    LOAD Min(FieldValue('$(parFromField).Floor', RecNo())) as [~MinDate]
    AUTOGENERATE FieldValueCount('$(parFromField).Floor');
    LET vCalMinDate= Num(peek('~MinDate', 0, '~tmpMinDate'), '','.',' ');
    DROP TABLE [~tmpMinDate];
    
    TRACE Looking for max date of field "$(parToField)";
    [~tmpMaxDate]:
    LOAD Max(FieldValue('$(parToField).Floor',RecNo())) as [~MaxDate]
    AUTOGENERATE FieldValueCount('$(parToField).Floor');
    LET vCalMaxDate= Num(peek('~MaxDate', 0, '~tmpMaxDate'), '','.',' ');
    DROP TABLE [~tmpMaxDate];

    // Phase 3) Build a table ~Cuts which has as many cut dates as 
    // contained between the min FromDate and max ToDate
    TRACE SUB SplitTimeSpans - Phase 3;
    [~Cuts]:
    LOAD
        Num((RowNo() -1) * $(subResolution) + $(vCalMinDate),'','.',' ') as [$(subCutDateField)]
    AUTOGENERATE (($(vCalMaxDate) - $(vCalMinDate))/$(subResolution) + 1);


    // Phase 4) Split the rows in the data table into multiple rowse
    // using inner join and interval match
    TRACE SUB SplitTimeSpans - Phase 4 (Interval Match);
    
    [$(parTable)~IM]:
    INTERVALMATCH([$(subCutDateField)])
    LOAD DISTINCT [$(parFromField).Floor], [$(parToField).Floor]
    RESIDENT [$(parTable)~1];
    DROP TABLE [~Cuts];
 
    [$(parTable)~Exploder]:
    MAPPING LOAD 
    	Hash256([$(parFromField).Floor], [$(parToField).Floor]),
        Concat([$(subCutDateField)], CHR(9))
    RESIDENT        
    	[$(parTable)~IM]
    GROUP BY
        [$(parFromField).Floor], [$(parToField).Floor];
    DROP TABLE [$(parTable)~IM];

/*    
    Using ApplyMap + SubField to avoid large tables to join
    TRACE SUB SplitTimeSpans - Phase 4 (Joining);
    INNER JOIN ([$(parTable)~1]) 
    LOAD DISTINCT
        // JOIN ON
        [$(parFromField).Floor], [$(parToField).Floor],
        // Add field
        [$(subCutDateField)]
    RESIDENT [$(parTable)~IM];
    DROP TABLE [$(parTable)~IM];
*/

// Phase 5) create final table
    TRACE SUB SplitTimeSpans - Phase 5 ;
     
    [$(parTable)~2]:
    LOAD 
    	*,
       //TimeStamp([$(parToField).New] - [$(parFromField).New], 'h:mm:ss') AS [$(subDurationField)],
       Num([$(parToField).New] - [$(parFromField).New]) AS [$(subDurationField)],
       Fabs([$(parFromField).New]=[$(parFromField)]) AS [$(parFromField).IsOrig]
    ;    
    LOAD 
    	*,   
    	TimeStamp(RangeMax([$(parFromField)], [$(subCutDateField)]), '$(parFormat)') AS [$(parFromField).New],
        TimeStamp(RangeMin([$(parToField)], [$(subCutDateField)]+ $(subResolution)), '$(parFormat)') AS [$(parToField).New]
    ;       
    LOAD // Explode rows into multiple rows using ApplyMap and SubField (to avoid large tables to join)
        $(subFormatFunct) (SubField(  // this "syntax-error" is okay
           ApplyMap('$(parTable)~Exploder',Hash256([$(parFromField).Floor], [$(parToField).Floor]), Null())
        ,Chr(9)) $(subFormatParam)) AS [$(subCutDateField)],    
        *
    RESIDENT 
        [$(parTable)~1];

    DROP TABLE [$(parTable)~1];      
    DROP FIELDS [$(parFromField).Floor], [$(parToField).Floor] FROM [$(parTable)~2]; 

    LET subRowsAfter = NoOfRows('$(parTable)~2');
    TRACE Table "$(parTable)" had $(subRowsBefore) rows, has now $(subRowsAfter) rows;
 
    // Phase 6) Make a final check that the durations haven't changed
    TRACE SUB SplitTimeSpans - Phase 6;
    IF subCheckSums THEN
        TRACE Creating Checksum (2) ...;
        [~CheckSums2]:
        LOAD 
        	Sum([$(subDurationField)]) AS [~CheckSum2] 
        RESIDENT [$(parTable)~2];
        LET vCheckSum2 = Peek('~CheckSum2',0,'~CheckSums2');
        LET subCheckSumDiff = Num(Fabs(vCheckSum1 - vCheckSum2),'0.0000000','.',' ');
        TRACE CheckSum1: $(vCheckSum1) | CheckSum2: $(vCheckSum2) | Diff: $(subCheckSumDiff);        
        IF subCheckSubDiff > subCheckSubTolerance THEN
            [Error: The total duration has changed after the split-operation. See ~CheckSums table.];
        ELSE 
            DROP TABLE [~CheckSums2];
        END IF        
    END IF
    
    // Phase 7) Clean up
    RENAME TABLE [$(parTable)~2] TO [$(parTable)];
    WHEN subCutDateField = '$(parTable)~CutDate' DROP FIELD [$(parTable)~CutDate] FROM [$(parTable)];
    WHEN subDurationField = '$(parTable)~Duration' DROP FIELD [$(parTable)~Duration] FROM [$(parTable)];
    DROP FIELD [$(parFromField)];
    DROP FIELD [$(parToField)];
    RENAME FIELD [$(parFromField).New] TO [$(parFromField)];
    RENAME FIELD [$(parToField).New] TO [$(parToField)];
    //RENAME FIELD [$(parFromField)] TO [$(parFromField).Original];
    //RENAME FIELD [$(parToField)] TO [$(parToField).Original];
    //RENAME FIELD [$(parFromField).New] TO [$(parFromField)];
    //RENAME FIELD [$(parToField).New] TO [$(parToField)];
    // remove temp variables (created inside this sub)
    LET subDurationField = Null();
    LET subCutDateField = Null();
    LET subCutFormat = Null();
    LET subResolution = Null();
    LET subRowsBefore = Null();
    LET subCheckSums = Null();
    LET subFormatFunct = Null();
    LET subFormatParam = Null();
    LET subCheckSumDiff = Null();
    LET subCheckSumTolerance = Null();
    LET vCalMinDate = Null();
    LET vCalMaxDate = Null();
    LET subV = Null();
    LET subLoadBlock = Null();
END SUB