CODEWITHSUNDEEP
sql-server
Chappy
Chappy

Reputation: 47

SQL Duration that value was true

Edited: to include sample data

Looking for guidance on a TSQL query.

I have a table that stores readings from a sensor (Amperage). The table basically has a Date/Time and a Value column.

The date/time increments every 5 seconds (a new record is added on 5 second intervals).

I am trying to build a query to determine the duration of time that the value was >X.

Example Data: http://sqlfiddle.com/#!18/f15c0/1/0

The example data is missing chunks to make it smaller but think you would get the idea.

I am trying to get the first record to the next record that goes above 7. This I would do a datediff to get the duration in seconds from when the data started to that first record over 7. I then need to repeat this but now find when it goes below 7.

This way I can see the cycle time duration.

Think of it as your Fridge. The sensor checks in every 5 seconds and sees that the fridge is off and records that. Eventually the fridge turns on and remains on for a period of time. I am trying to get all those cycle times. I am trying to use Lead and Lag functions...but just getting lost in regards to pulling the data.

Any help?

Upvotes: 0

Views: 117

Answers (3)

dbenham
dbenham

Reputation: 130879

Images are blocked at my current location, so I can't see your structure. I'll assume you have the following table (I'll ignore PK and other constraints):

create table reading(
  entryDate datetime,
  amps int
)

Assuming anything above 3 amps is ON, and you want to compute the duty cycles in seconds, then

declare @threshold int = 3;
with
state as (
  select entryDate,
         case when amps>@threshold then 'ON' else 'OFF' end state,
         lag( case when amps>@threshold then 'ON' else 'OFF' end )
           over(order by entryDate) prev_state
    from reading
),
transition as (
  select entryDate, state
    from state
   where state <> coalesce(prev_state,'')
)
select entryDate,
       state,
       dateDiff( 
         s,
         entryDate,
         lead(entryDate) over(order by entryDate)
       ) duration
  from transition
 order by 1

Upvotes: 0

LukStorms
LukStorms

Reputation: 29667

Not sure how fast it'll be, but if you want to try with LAG?

Here's an example that checks for a difference of X>=2

SELECT entrydate, amps
FROM
(
    SELECT
     entrydate, amps,
     amps - LAG(amps) OVER (ORDER BY entrydate) AS PrevAmpsDiff
    FROM YourTable
) q
WHERE ABS(FLOOR(PrevAmpsDiff)) >= 2
ORDER BY entrydate;

A test on rextester here

Upvotes: 0

Max Zolotenko
Max Zolotenko

Reputation: 1132

declare @val    numeric(10,5) = 7.0

select  v1.entrydate,
        v1.Amps,
        case when v1.fl = 1 and v1.lg is null then 1
            when v1.lg != v1.fl then 1
            else 0
        end as fl_new
from    (
            select  v1.entrydate,
                    v1.Amps,
                    case when v1.Amps > @val then 1
                        else 0
                    end         as  fl,
                    lag(case when v1.Amps > @val then 1
                        else 0
                    end)    over(order by v1.entrydate) as lg
            from    (
                        select  t.entrydate     as  entrydate,
                                t.Amps          as  Amps
                        from    YourTable t
                    ) v1
        ) v1
where   case when v1.fl = 1 and v1.lg is null then 1
            when v1.lg != v1.fl then 1
            else 0
        end = 1
order by v1.entrydate

And don't forget set YourTable name and @val (which is "X").

Upvotes: 2

Related Questions