CODEWITHSUNDEEP
sqlsql-server
Consol
Consol

Reputation: 13

SQL Query to return 15 minute totals from a totaliser integrating over 24 hours

I have a number of electricity meters connected to a PLC (Programmable Logic Controller).
The PLC counts (integrates) the kWh pulses from the meters over a 24 hour period.
The count is reset at midnight.
The current count value is logged to a table every second.

I need to retrieve the kWh total in each 15 minute period for a meter.

E.g.: 

Meter count at 11:00:00 = 1000  
Meter count at 11:14:59 = 1110  
Meter count at 11:29:59 = 1200       
Meter count at 11:59:59 = 1400

15 Minute kWh totals: 

At 11:14:59 = 110  
At 11:29:59 = 90  
At 11:59:59 = 200

Basically, I want to subtract the count at a 15 minute period from the count at a previous 15 min period.

The database is MSSQLServer.
Is it possible to return the above using a select query.
I need to export this data to a csv file.

Upvotes: 1

Views: 583

Answers (3)

Serpiton
Serpiton

Reputation: 3684

I posted this as a separated answer as it uses a different approach to the question, if that's not the right thing to do I will edit my previous answer to add this one.

To get the result as in the question it's possible to exploit an intrinsic property of the data: the consumed kW are non decreasing, this means that the minimum value will always be the first and the maximum value will always be the last of every block.

The quick & dirty query that uses those property is

SELECT MAX(_Time) _Time
     , BlockConsume = MAX(KWh) - MIN(KWh)
FROM   UtilityMeter
GROUP BY DateDiff(mi, 0, _Time) / 15
ORDER BY _Time

the group anchor change at the exact quarter of hour, MAX(_Time) will be the last value before an exact quarter: 14.59, 29.59, 44.59, 59.99.

The query works, but it's a table scan, to get a little faster it's possible to remove all the unused data by filtering out all the 898 (15*60-2) rows that are not the first of the last of a group

WITH Quarter AS (
  SELECT _Time, KWh
       , DateDiff(mi, 0, _Time) / 15 Block
  FROM   UtilityMeter
  WHERE  DateDiff(s, 0, _Time) % (900) IN (0, 899)
)
SELECT MAX(_Time) _Time
     , BlockConsume = MAX(KWh) - MIN(KWh)
FROM   Quarter
GROUP BY Block
ORDER BY _Time

Upvotes: 0

Serpiton
Serpiton

Reputation: 3684

The following queries will get the data on the quarters of hour, not at a second before that as in the OP data.

The OP didn't provide the table schema, this answer use this

CREATE TABLE UtilityMeter (
  _Time Time
, KWh Int
)

The server is supposed to be SQLServer 2008 or better (to use the TIME type)

The first thing to do is to filter the data to get only the quarters

SELECT _Time, KWh
FROM   UtilityMeter
WHERE  _Time = Cast(DateAdd(mi, DateDiff(mi, 0, _Time) / 15 * 15, 0) as Time)

DateDiff return an integer so minutes / 15 is an integer division and minutes / 15 * 15 will not returns minutes but the quarter before it.

Now if the SQLServer is 2012 of better it's possible to use LAG

With Quarter AS (
  SELECT _Time, KWh
  FROM   UtilityMeter
  WHERE  _Time = Cast(DateAdd(mi, DateDiff(mi, 0, _Time) / 15 * 15, 0) as Time)
)
SELECT _Time
     , BlockConsume = KWh - LAG(KWh, 1, 0) OVER (ORDER BY _Time)
FROM   Quarter;

otherwise an auto-JOIN is needed

With Quarter AS (
  SELECT _Time, KWh
       , ID = Row_Number() OVER (ORDER BY _Time)
  FROM   UtilityMeter
  WHERE  _Time = Cast(DateAdd(mi, DateDiff(mi, 0, _Time) / 15 * 15, 0) as Time)
)
SELECT _1._Time
     , BlockConsume = _1.KWh - _2.KWh
FROM   Quarter _1
       INNER JOIN Quarter _2 ON _1.ID = _2.ID + 1

the calculated ID added in the CTE is to simplify the JOIN condition.

SQLFiddle demo with both queries and generated data

Upvotes: 1

xacinay
xacinay

Reputation: 909

I think CTE and self join is enough to achieve the desired result:

--constructing ID
WITH vPLC as (select *, ROW_NUMBER() OVER (order by date) as recID  
from @PLC
)
select vPLC.*, COALESCE( vPLC.value - n.Value, 0 ) as diffVal
from vPLC
     left JOIN vPLC n on n.recID = vPLC.recID - 1

----------OUTPUT----------
recId  date                 value   diffVal
1      6/5/2014 11:00:00 AM 1000    0
2      6/5/2014 11:14:59 AM 1110    110
3      6/5/2014 11:29:59 AM 1200    90
4      6/5/2014 11:59:59 AM 1400    200

Proof code is here

Upvotes: 2

Related Questions