Grouping and checking for conditions using a query

Question

I have been asked to compile a SQL query for the following case:

ID | PARAMETER  | VALID | VALUE
------------------------------
1  | Event1_Val | 1     | 0
1  | Event2_Val | 0     | 50.00
1  | Event2_Avg | 0     | 55.00
1  | Event1_Avg | 0     | 66.67

2  | Event1_Val | 0     | 37.50
2  | Event2_Val | 1     | 0
2  | Event1_Avg | 0     | 50.00
2  | Event2_Avg | 0     | 66.67

where _Val is a current value. _Avg is the average of the past five values.

Requirement 1: If VALID == 1 (applies only to _Val values), do not check that parameter's _Val and _Avg, but check the other parameter's _Val and _Avg. In the example above for ID=1, Event2_Val and Event2_Avg would be checked because Event1_Val.VALID = 1 (which means Event1_Val and Event1_Avg would not be checked.)

Requirement 2: Check the values of _Val and _Avg as a result of Requirement 1 for the following conditions:

• _Val < 5 OR
• _Val + 10 < _Avg

The conditions above return an alert. In the example above for ID=2, Event1_Val will return an alert because 37.50 + 10 is less than 50.00.

Requirement 3: Indicate the results of Requirement 2

ID | PARAMETER  | ALERT
------------------------
1  | Event2_Val | 0 
2  | Event1_Val | 1

---

I have tried to check for VALID does not equal 1, but that leaves me with the following:

ID | PARAMETER  | VALID | VALUE
------------------------------
1  | Event2_Val | 0     | 50.00
1  | Event2_Avg | 0     | 55.00
1  | Event1_Avg | 0     | 66.67

2  | Event1_Val | 0     | 37.50
2  | Event1_Avg | 0     | 50.00
2  | Event2_Avg | 0     | 66.67

I do not want to check for the parameter for the event in which _Val.VALID == 1. Meaning, for ID=1, I do not want to include Event1_Avg.

---

Is there a way to group and check conditions using a query for this case? NOTE: There is a strong desire not to change values in the tables.

Answer 1

Try:

SELECT t1.id,
   t1.parameter,
   CASE 
      WHEN (t1.value < 5 OR (t1.value + 10 < t2.value)) THEN 1
      ELSE 0
   END alert
FROM your_table t1
JOIN your_table t2 ON t1.id = t2.id
   AND left(t1.parameter, 6) = left(t2.parameter, 6)
   AND t1.parameter LIKE '%_Val'
   AND t2.parameter LIKE '%_Avg'
WHERE 
   t1.valid <> 1

Answer 2

Here you go, some comments explaining whats going on:

declare @events table
(
ID int
,PARAMETER nvarchar(25)
,VALID bit
,VALUE decimal (18,2)
)

insert into @events(ID, PARAMETER,VALID, VALUE)

select 1  , 'Event1_Val' , 1     , 0
union all
select 1  , 'Event2_Val' , 0     , 50.00
union all
select 1  , 'Event2_Avg' , 0     , 55.00
union all
select 1  , 'Event1_Avg' , 0     , 66.67
union all
select 2  , 'Event1_Val' , 0     , 37.50
union all
select 2  , 'Event2_Val' , 1     , 0
union all
select 2  , 'Event1_Avg' , 0     , 50.00
union all
select 2  , 'Event2_Avg' , 0     , 66.67


select * from @events e

SELECT 
     ID
    ,PARAMETER 
    ,CASE WHEN (VALUE < 5 OR VALUE + 10.00 < AVRG) THEN 1 ELSE 0 END AS ALERT
FROM
(
    select 
        e.ID,
        MAX(CASE WHEN RIGHT(e.PARAMETER,4) = '_Val' THEN e.PARAMETER END ) as PARAMETER,
        MAX(CASE WHEN RIGHT(e.PARAMETER,4) = '_Val' THEN e.VALUE END) as VALUE,
        MAX(CASE WHEN RIGHT(e.PARAMETER,4) = '_Avg' THEN e.VALUE END) as AVRG
    from @events e
    JOIN
    ( --Get Valid Events
        select ID, REPLACE(REPLACE(PARAMETER,'_Val',''),'_Avg','') as Parameter --returns EventNAme without '_Val' or '_Avg' for grouping porpuses
        from @events
        WHERE RIGHT(PARAMETER,4) = '_Val' and VALID = 0
        GROUP BY ID ,REPLACE(REPLACE(PARAMETER,'_Val',''),'_Avg','')
    ) validEvents --filter by valid events only
        on validEvents.ID = e.ID
        AND validEvents.Parameter = REPLACE(REPLACE(e.PARAMETER,'_Val',''),'_Avg','')
    GROUP BY e.ID
) PIVOTEDRESULTS