SQL Server 2012: Get Number of Occurrences and Calculate Duration Using Dynamic SQL

Question

I have a table of process setpoint and process value (PV).

Something like the table below:

    Timestamp       Setpoint             PV 
    ---------       ---------       -------------
    t1                  100             125
    t2                  100              95  *
    t3                  100              98
    t4                  100              88
    t5                  100             105 
    t6                  100              59  *
    t7                  100              90
    t8                  100             101
    t9                  100              70  *
    t10                 100             101

What I want to do is request to create one line as a result in order to count the number of times that the PV has dropped below the Setpoint and will return above the set value. and also calculate the length of time when it was below a given value.

The result should be like this.

    NumberofOccurance       Duration
    ------------------      ---------
            3               (t5-t2)+(t8-t6)+(t10-t9)

Note: The correct number of events 3 in this case is not 6, and I think the most difficult part is the duration.

Any ideas?

Thanks in advance

: , , PV ( 10 PV PV, 20 . . 1 . , PV? SQL CTE . .

Answer 1

,

CREATE TABLE MyTable
  (
     TimeSt   INT,
     SetPoint INT,
     PV       INT
  )

INSERT INTO MyTable
VALUES      (1,             100,             122),
            (2,             100,             95),
            (3,             100,             98),
            (4,             100,             88),
            (5,             100,             105),
            (6,             100,             59),
            (7,             100,             90),
            (8,             100,             101),
            (9,             100,             70),
            (10,             100,             101);

WITH CTE
     AS (SELECT
           *
           ,lag(TimeSt, 1) OVER(ORDER BY TimeSt) AS LagTimeSt
           ,lag(SetPoint, 1) OVER(ORDER BY TimeSt) AS LagSetPoint
           ,lag(PV, 1) OVER(ORDER BY TimeSt) AS LagPV
         FROM
           MyTable),
     CTE2
     AS (SELECT
           *
           ,CASE
              WHEN (PV < SetPoint AND LagPV > LagSetPoint) 
                THEN 1 ELSE 0 END AS FirstDrop
         FROM
           CTE
         WHERE
          (PV < SetPoint AND LagPV > LagSetPoint)
           OR (PV > SetPoint AND LagPV < LagSetPoint)),
     CTE3
     AS (SELECT
           Lead(timest) OVER(ORDER BY TimeSt) UpTime
           ,*
         FROM
           CTE2)
SELECT
  sum(firstDrop)        AS Occur
  ,sum(uptime - Timest) AS DownTime
FROM
  CTE3 
Where FirstDrop = 1

Answer 2

lag, pv , pv , pv :

select *
from (
    select *,
        lag(Setpoint) over (order by Timestamp) previous_setpoint,
        lag(PV) over (order by Timestamp) previous_pv
    from Table1
) t where PV < Setpoint
and previous_pv >= previous_setpoint

, pv i.e. -t2 -t6 -t9. . . , , previous_pv. next_pv, previous_pv.

Timestamp 1,2,3 t2 t6 t9. prev_timestamp 2 t5 prev_timestamp 3 ( ) t8 next_timestamp 3 ( ) t10

select count(*), sum(case 
            when rn_asc = 1 then -Timestamp
            when rn_desc = 1 then -Timestamp + next_timestamp + prev_timestamp
            else -Timestamp + prev_timestamp
            end)
from (
    select *, 
     row_number() over (order by Timestamp) rn_asc,
     row_number() over (order by Timestamp desc) rn_desc
    from (
        select *,
            lag(Setpoint) over (order by Timestamp) previous_setpoint,
            lag(PV) over (order by Timestamp) previous_pv,
            lag(Timestamp) over (order by Timestamp) prev_timestamp,
            lead(Timestamp) over (order by Timestamp) next_timestamp
        from Table1
    ) t where PV < Setpoint
    and previous_pv >= previous_setpoint
) t

http://sqlfiddle.com/#!6/b6ac5/5

Answer 3

. ,

• .

WITH T0 AS
(
    SELECT [Timestamp], CASE WHEN PV >= SetPoint THEN 1 ELSE 0 END AS pvType
    FROM table
),
T1 AS
(
    SELECT [Timestamp], 
        CASE 
            WHEN LAG(pvType) OVER(ORDER BY [Timestamp]) = 1 AND pvType = 0 THEN 1 
            ELSE 0 
        END AS pvStart,
        CASE 
            WHEN LAG(pvType) OVER(ORDER BY [Timestamp]) = 0 AND pvType = 1 THEN 1 
            ELSE 0 
        END AS pvEnd
    FROM T0
),
T2 AS 
(
    SELECT [Timestamp] AS timestampStart, 
        CASE
            WHEN pvEnd = 1 THEN [Timestamp] 
            ELSE LEAD([Timestamp]) OVER(ORDER BY [Timestamp]) 
        END AS timestampEnd, 
        pvStart
    FROM T1
    WHERE pvStart = 1 OR pvEnd = 1
)
SELECT 
    COUNT(*) AS Occurance, 
    -- Depending Timestamp type, you may want DATEDIFF
    SUM(timestampEnd - timestampStart) AS Duration
FROM T2
WHERE pvStart = 1