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

[#1]

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!")
);

[#2 · Accepted solution]

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
	)
);

[#3]

Thank you Jim.

Worked!  

Cheers!

[#4]

If this is how your data looks like (even hours) and the matches are defined like in your script, you should be able to do this with double join/update. Add Matched column to your Events table

and then first join using Round_date and Round_when_started

Then for second join use Round_date and round_when_ended.

And of course you can use something like Jim did demonstrate.

[#5]

Hi Jarmo,

Sorry I should have posted that I am using JMP and not JMP Pro.

It never crossed my mind to double join and may try that in Query Builder.

Cheers! 

[#6]

I think you shouldn't need JMP Pro for what I did. I just used Tables > Update (should be available with standard JMP) twice but you could also do it with a single query using Query Builder

I added one calculated column

View more...

SQL query is

SELECT t1.Round_date, t1."Building pressure", t1."Plant Temp", t1."Zone Humdity", 
	t1."outside air temp", t3.Round_when_ended IS NOT NULL  OR  t2.Round_when_started IS NOT NULL AS Calc1, t3.Round_when_ended IS NOT NULL  OR  t2.Round_when_started IS NOT NULL AS "Calc1 2" 
FROM HVAC  t1 
	LEFT OUTER JOIN Events t3 
		ON  ( t1.Round_date = t3.Round_when_ended )  
	LEFT OUTER JOIN Events t2 
		ON  ( t1.Round_date = t2.Round_when_started );

And JMP's query builder script is something like this

New SQL Query(
	Version(130),
	Connection("JMP"),
	JMP Tables(["Events" => "\C:\Users\jarmo\Downloads\Events.jmp", "HVAC" => "\C:\Users\jarmo\Downloads\HVAC.jmp"]),
	QueryName("SQLQuery1"),
	Select(
		Column("Round_date", "t1", Numeric Format("m/d/y h:m:s", "0", "NO", "")),
		Column("Building pressure", "t1", Numeric Format("Fixed Dec", "0", "NO", "")),
		Column("Plant Temp", "t1", Numeric Format("Fixed Dec", "0", "NO", "")),
		Column("Zone Humdity", "t1", Numeric Format("Fixed Dec", "0", "NO", "")),
		Column("outside air temp", "t1", Numeric Format("Fixed Dec", "0", "NO", "")),
		ExprLookup("Calc1", JMP Name("Calc1"))
	),
	From(
		Table("HVAC", Alias("t1")),
		Table(
			"Events",
			Alias("t3"),
			Join(
				Type(Left Outer),
				EQ(
					Column("Round_date", "t1", Numeric Format("m/d/y h:m:s", "0", "NO", "")),
					Column("Round_when_ended", "t3", Numeric Format("y/m/d h:m:s", "0", "NO", ""))
				)
			)
		),
		Table(
			"Events",
			Alias("t2"),
			Join(
				Type(Left Outer),
				EQ(
					Column("Round_date", "t1", Numeric Format("m/d/y h:m:s", "0", "NO", "")),
					Column("Round_when_started", "t2", Numeric Format("y/m/d h:m:s", "0", "NO", ""))
				)
			)
		),
		Expression("t3.Round_when_ended IS NOT NULL  OR  t2.Round_when_started IS NOT NULL", Alias("Calc1"))
	),
	Where(Custom("", UI(Custom(Base("Continuous")))))
) << Run;