Cfrgtkky

I have a select clause that averages readings for the hour and gives an aggregate score for them.

The select looks like this (image is similar to the code)
Original Select Clause

SELECT DISTINCT 

            DATEADD(hh,DATEPART(hh,logger_time), CAST(CAST(logger_time AS DATE) AS DATETIME)) AS score_time,

            CASE 

                WHEN AVG(probe_temp) BETWEEN 100 and 200 THEN 100

                WHEN AVG(probe_temp) BETWEEN 200 and 300 THEN (100-((ABS(AVG(probe_temp)- 200/abs(210-200)) * (100-60))))

            END AS probe_temp_acc,



            CASE 

                WHEN AVG(converter_temp) BETWEEN 700 and 750 THEN 100

                WHEN AVG(converter_temp) BETWEEN 750 and 810 THEN (100-((ABS(AVG(converter_temp)- 750/abs(805-750)) * (100-60))))

            END AS converter_temp_acc



    FROM tblDeviceReading

    GROUP BY DATEADD(hh,DATEPART(hh,logger_time),CAST(CAST(logger_time AS DATE) AS DATETIME)) 

    ORDER BY score_time 

The sample data that we need to aggregate looks like this:

1. logger_time-----------------probe_temp------converter_temp


2. 2018-11-10 21:44:00.000-----210.3837--------759.3838


3. 2018-11-10 21:45:00.000-----219.8789--------761.1938

I need to join another table (below) to replace the hard coded values with data below. By this I mean that when I am looking at the probe_temp device I want to use aLow, aHigh, bLow, bHigh instead of the hard coded values of 100, 200, etc in the WHEN AVG(probe_temp) BETWEEN 200 and 300 THEN... case statement.

1. sig---------------aLow-------aHigh-------bLow--------bHigh




2. probe_temp-------220.0000---235.0000-----210.0000----245.0000




3. converter_temp---760.0000---800.0000-----740.0000----805.0000

So the first select:

CASE 

    WHEN AVG(probe_temp) BETWEEN 100 and 200 THEN 100

    WHEN AVG(probe_temp) BETWEEN 200 and 300 THEN (100-((ABS(AVG(probe_temp)- 200/abs(210-200)) * (100-60))))

END AS probe_temp_acc

would need to look something like this:

CASE 

    WHEN AVG(probe_temp) BETWEEN aLow and aHight THEN 100

    WHEN AVG(probe_temp) BETWEEN bLow and bHigh THEN (100-((ABS(AVG(probe_temp)- bLow/abs(bHigh-bLow)) * (100-60))))

END AS probe_temp_acc

Is there a way I can join the new table to the existing query and keep the aggregation over the group by clause? I looked at doing this in a cursor (the table of values will only have about 25 rows in it) but was wondering if there was a better way.

I think that philipxy is correct, that I have to use temporary tables to store the results then select at the end to get the results needed.

Results needed

Basically I have new non-hard coded limiters that I need to insert into the original query. The new data is in a row based table but the readings that need to be aggregated are in column values. I am unsure how to join these tables.

Thank you