I have two tables in MS SQL Server 2012. The first is a minute by minute production record which looks like (can't guarantee every single minute gets its own record, but never two records for one minute):
[myDateTime] [Produced] 12:01 100 12:02 110 12:03 115 12:04 100
The second table is a journal-style log of whether to consider the [Produced] column from Table 1 or not.
[myStartDateTime] [myEndDateTime] 12:02 12:03 12:47 12:58
I want to be able to look through each row of Table 1 and either take the [Produced] column, or take a NULL value, depending on if that minute falls within a record of Table 2 (ie between myStartDateTime and myEndDateTime INCLUSIVE).
In the example above, that would look like:
[myDateTime] [Produced] 12:01 100 12:02 NULL 12:03 NULL 12:04 100
I can't seem to get this to work (in code, or in my head). In my head I think I should join on myStartDateTime and join on myEndDateTime and make a new column called [Include] and set it to 1 or 0 depending on whether I want to include it or not. Each table might have 300 - 400 thousand rows and everything I am trying is slow.
Any help would be appreciated.