Petre Manolescu Petre Manolescu - 1 year ago 57
SQL Question

Select whole period of time in a group when at least one value in the column is true

I am trying to write a select statement that would enable me to view the whole period of time if the constraint is true at least once. Attached is a snippet of the table that I am working with. Featured are aluminium electrolysis cells called A042 and A043 (mytable.pot), date_time (mytable.dags), the constraint, boolean, vanadium concentration (mytable.V_low) and the row number (mytable.Rn). My table As you see the constraint is true only for 3 days of the 9 day period featured for A042, but I want the query to return all dates before and after the constraint is 1. In other words, if it is 1 at least once, it should return the whole 9 day period for the given pot.

The reason is that there are many pots to inspect and this value is usually 0. Therefore when an anomaly is detected I need a report on the whole period for the given pot. In the example shown here it should only return the 9 day period for pot A042 since the constraint is 0 for all the A043 days.

How can I create another column (mytable.Low_period) that would be 1 for all the days of pot A042 but 0 for all A043, so that I could then easily end the statement with (where mytable.Low_period = 1) ? Other suggestions are also welcome.

I do not have admin access to create procedures and/or alter tables.

Thanks in advance

My table

Answer Source

One method uses window functions:

select t.*
from (select t.*, max(v_low) over (partition by pot) as max_v_low
      from t
     ) t
where max_v_low > 0;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download