Sql Runtime Reports

I have a query which displays information from a table like this. SELECT tag.label, inputs.input_raw, inputs.unix_timestamp, tag.inverted FROM inputs JOIN tag ON tag.tag_id = inp

Solution 1:

You want to use the Lead orLag function to compare the current result with either the previous or next. These functions, however, are introduced in SQL Server 2012.

With the help of Mr. pinaldave I managed to produce the following SQL Fiddle that counts every change from 0 to 1.

;WITH x AS 
(
  SELECT1AS ldOffset, -- equiv to2nd param of LEAD
    1AS lgOffset, -- equiv to2nd param of LAG
    NULL AS ldDefVal, -- equiv to3rd param of LEAD
    NULL AS lgDefVal, -- equiv to3rd param of LAG
    ROW_NUMBER() OVER (ORDERBY unix_timestamp) AS row,
    label,
    input_raw,
    unix_timestamp,
    inverted
  FROM inputs
 )
SELECT 
    COUNT(1)
FROM x
LEFT OUTER JOIN x AS xLg 
  ON x.row = xLg.row + x.lgOffset
WHERE xLg.input_raw = 0AND x.input_raw = 1;

You can use the same Lag function to calculate the difference between the current timestamp and the previous timestamp.

EDIT:

This SQL Fiddle should show how to get the total time the pump is running (you'll have to define running yourself. This query now assumes that going from 0 to 1 or staying 1 is running. You should also double check my timestamp calculations cause I've never used unix timestamps before.

;WITH x AS 
(
  SELECT1AS ldOffset, -- equiv to2nd param of LEAD
    1AS lgOffset, -- equiv to2nd param of LAG
    NULL AS ldDefVal, -- equiv to3rd param of LEAD
    NULL AS lgDefVal, -- equiv to3rd param of LAG
    ROW_NUMBER() OVER (ORDERBY unix_timestamp) AS row,
    label,
    input_raw,
    unix_timestamp,
    inverted
  FROM inputs
 )
SELECT 
    SUM(DATEDIFF(mi, 
             DATEADD(ss, xLg.unix_timestamp,'01/01/1970'), 
             DATEADD(ss, x.unix_timestamp,'01/01/1970')))  FROM x
LEFT OUTER JOIN x AS xLg 
  ON x.row = xLg.row + x.lgOffset
WHERE 
  (xLg.input_raw = 0AND x.input_raw = 1)
OR
  (xLg.input_raw = 1AND x.input_raw = 1);

EDIT 2:

I guess the easiest way to check for inverted is the change the WHEREclause to something like:

WHERE 
  (x.inverted = 1 AND xLg.input_raw = 0 AND x.input_raw = 1)
OR
  (x.inverted = 0 AND xLg.input_raw = 1 AND x.input_raw = 0)