SQL Server – How to Get Average According to Last Update Time

sql serversql-server-2008

There is table – Item below, how can I get the average Value according to its 15 minute value from last record time? I am using SQL Server 2008.

Table — Item

enter image description here

Table 15min_AVG

enter image description here

Best Answer

Assuming your table and sample data look like this:

CREATE TABLE dbo.Item
(
    Item        char(1) NOT NULL,
    RecordTime  datetime2(0) NOT NULL,
    Value       decimal(5,3) NOT NULL,

    CONSTRAINT PK_dbo_Item_Item_RecordTime
        PRIMARY KEY (Item, RecordTime)
);

INSERT dbo.Item
    (Item, RecordTime, Value)
VALUES
    ('A', '2014-12-29T10:13:00', 0.139),
    ('A', '2014-12-29T10:12:00', 0.150),
    ('A', '2014-12-29T10:11:00', 0.113),
    ('B', '2014-12-29T10:10:00', 0.139),
    ('B', '2014-12-29T10:09:00', 0.120),
    ('B', '2014-12-29T10:08:00', 0.084),
    ('C', '2014-12-29T09:55:00', 0.101),
    ('C', '2014-12-29T09:54:00', 0.095),
    ('C', '2014-12-29T09:53:00', 0.129);

One way to write the query is:

SELECT
    MaxTime.Item,
    Average.Value
FROM 
(
    -- Highest record time per item
    SELECT
        I.Item,
        RecordTime = MAX(I.RecordTime)
    FROM dbo.Item AS I
    GROUP BY
        I.Item
) AS MaxTime
CROSS APPLY 
(
    -- Average value within 15 minutes
    SELECT
        Value = CONVERT(decimal(5,3), AVG(I2.Value))
    FROM dbo.Item AS I2
    WHERE
        I2.Item = MaxTime.Item
        AND I2.RecordTime >= DATEADD(MINUTE, -15, MaxTime.RecordTime)
) AS Average
ORDER BY
    MaxTime.Item;

SQLFiddle Demo

Related Question