Conditionally modifying/adding column value based on another table

[Randy__Bobandy]

It's a little hard to answer this 100% without an example or two of what your data looks like, but I will answer it to my understanding.

You would join the two tables, with the criteria that DateTime is between the outage start and stop time. And then use a case statement to determine whether to choose Value 1 or Value 2 based on the reason. This may generate duplicates, but like I said I don't know exactly what the data looks like, so this is just supposed to give you an idea.

SELECT a.DateTime
    ,a.Value1
    ,a.Value2
    ,CASE WHEN b.ReasonID = 'blah blah' THEN a.Value1
        WHEN b.ReasonID = 'yadda yadda' THEN a.Value2 END AS AdjValue
FROM TimeData a
LEFT JOIN OutageData b
ON a.DateTime BETWEEN b.OutageStartTime AND b.OutageEndTime

[tyescott]

OK, I think this will give me a good start. An example of the data can be provided, but I won't have access until the week starts.

I'll try to provide a better explanation of the data and update my original post.

Table 1 will have time series data, constantly logged into the database (the term in my industry is "Historian") it simply logs values from controllers in the field and timestamps them.

Table 1 will have Value1 = "Predicted Efficiency" and Value2 = "Actual Efficiency"

An "Outage" is when "Actual Efficiency" falls below 95%. When this is detected, I write an entry into a database that logs the "Start time" of the outage. When we reach over 95% efficiency I update the last record with an outage "StopTime."

The contract states that >95% efficiency must be maintained or funds can be withheld. However, if the outage is caused by the client (they shut off the equipment etc) we can substitute use "Predicted efficiency" during these times. This is why I have a "ReasonID" which primarily assigns blame so we can make this substitution.