SQL Finding max value of column during range defined by state of other column

Question

In the data below Event1 represents the end of a manufacturing cycle. Event2 happens during on a percentage of the cycles. I was wondering if it is possible to pull the end of cycle time (when Event1 returns to 0) and the maximum value of Event2 (either 0 or 1) during that cycle. Both values are binary.

    DateTime          Event1        Event2
    12/5/2012 07:00      1             0
    12/5/2012 07:01      0             0
    12/5/2012 07:45      1             0
    12/5/2012 07:46      0             0
    12/5/2012 07:50      0             1
    12/5/2012 07:54      0             0
    12/5/2012 08:30      1             0
    12/5/2012 08:31      0             0


    DateTime          max(Event2)
    12/5/2012 07:46      0
    12/5/2012 08:31      1

I'm using WonderWare historian database, so there are some unique options in the query. Below is what I use to find the end of cycle times.

SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime
FROM WideHistory
WHERE wwRetrievalMode = 'Delta'
AND Event1 = 1
AND wwEdgeDetection = 'TRAILING'
AND wwVersion = 'Latest'
AND DateTime >= '20121205 07:00'
AND DateTime <= '20121205 09:00'")

Is there a way to use the results of this query to generate DateTime restrictions of additional queries?

Wonderware seems quite related to SQL Server. Do you know which version of SQL Server it is based on? — Gordon Linoff, Dec 05 '12 at 16:30
It says Server software:Microsoft-IIS/6.0 Wonderware Information Server 4.0.100 and Build Version 1463.15.30.0 — Nick Antonakos, Dec 05 '12 at 16:41

score 0 · Answer 1 · answered Dec 05 '12 at 16:34

In a standard SQL database, I would characterize the periods by the end (when event1 happens) rather than the beginning of the period. The data contains the end, but not the beginning.

select EndOfCycleDateTime, count(*) as NumEvents,
       max(Event2) as maxEvent2
from (select t.*,
             (select min(datetime) from t t2 where t2.datetime >= t.datetime and t2.event1 = 1
             ) as EndOfCycleDateTime
      from t
     ) t
group by EndOfCycleDateTime

This is using a subquery to find the next time when event1 is 1. This should be the same value for everything in the cycle. The outer query just groups by this value.

score 0 · Answer 2 · answered Aug 23 '14 at 18:20

Wonderware uses SQL server standard edition (plus some fancy extra stuff on the historian) by default, so you should be able to use T-SQL to get what you want.

http://wonderwarenorth.com.s138768.gridserver.com/support/TechTip_1004_WonderwareHistorian&DifferentRetrievalMethods.pdf

SQL Finding max value of column during range defined by state of other column

2 Answers2