887979_en-US

liliconcom

Selecting all rows in a data table where date/time is between two values in another table

Hi,

I have two data tables named, HVAC and Events.  I want to match date/times in HVAC based on start time and end times of the Events table and mark the matching rows in HVAC table with 1.

 

I have script that runs but does not populate the "Matched" column and I am not quite sure why.

 

Names Default to Here (1);
Clear Symbols();
// Reference both tables
timeEvents = DataTable("Events");
timeHVAC = DataTable("HVAC");

// Get column references
startCol = timeEvents:Round_when_started;
endCol = timeEvents:Round_when_ended;
timeHVACCol = timeHVAC:Round_date;
matchedCol = timeHVAC:Matched;
Batch Interactive( 1 );

// Loop through timeEvents and tag matches
For(i = 1, i <= N Rows(timeEvents), i++,
    startTime = startCol[i];
    endTime = endCol[i];

    // Loop through HVAC and tag matching rows
    For(j = 1, j <= N Rows(timeHVAC), j++,
        eventTime = timeHVACCol[j];
        If(eventTime >= startTime & eventTime <= endTime,
            matchedCol[j] = 1;
        );
    );
);
Batch Interactive( 0 );

// Notify user
New Window("Script Completed",
    Text Box("Lucy, I'm home!")
);

Automation and ScriptingRe: Selecting all rows in a data table where date/time is between two values in another table

Here is how I would handle this

Names Default To Here( 1 );
Clear Symbols();
// Reference both tables
timeEvents = Data Table( "Events" );
timeHVAC = Data Table( "HVAC" );

For( i = 1, i <= N Rows( timeEvents ), i++,
	Try(
		timeHVAC:Matched[timeHVAC << get rows where(
			timeHVAC:Round_date >= timeEvents:Round_when_started[i] & timeHVAC
			:Round_date <= timeEvents:Round_when_ended[i]
		)] = 1
	)
);