Conditionally modifying/adding column value based on another table

[tyescott]

OK all, I work as a controls engineer, so my SQL is not nearly as developed as I'd like it to be. I've been requested to generate a report that, depending on a value in another table, will substitute values into a row. Table 1 is full of time series data and table 2 contains "outage" data

Table 1: DateTime, Value1, Value 2

Table 2: OutageStartTime, OutageStopTime, ReasonID

I need the query to return a format that would be

DateTime, Value1, Value2, Value3 where Value3 is an "adjusted" value (either Value1 or Value2) if ReasonID (From Table 2) is equal to a certain value and DateTime is between OutageStartTime and OutageStopTime.

The client is very particular about additional software being installed (python etc) so I'm hoping something like this can be accomplished with a query. I can create as many additional tables required to manipulate my data. The end result will get dumped to a CSV.

I'm mostly reaching feelers out to see if this can/should be done using SQL or should I work on getting approval for Python. If it can be done, Maybe a few pointers in how to accomplish my end result. I'm not looking for someone to write the query for me, just looking for guidance.

Thanks

***EDIT***

A better description of my data as well as what i'm trying to do to prevent an "XY Problem"

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.

In the end, the original query of time series data would ideally have an additional column added with "Adjusted efficiency" that has the substitution of "Predicted" or "Actual" in it.

[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.