Q2_15min, Sql Server

Q2_15min

CREATE TABLE table1(DateTime DATETIME, Val INTEGER);

INSERT INTO table1 values('2013-01-02 01:02:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:04:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:06:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:08:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:10:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:12:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:14:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:16:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:18:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:20:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:22:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:24:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:26:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:28:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:30:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:32:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:34:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:36:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:38:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:40:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:42:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:44:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:46:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:48:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:50:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:52:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:54:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:56:00.000', 1);
INSERT INTO table1 values('2013-01-02 01:58:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:00:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:02:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:04:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:06:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:08:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:10:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:12:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:14:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:16:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:18:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:20:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:22:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:24:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:26:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:28:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:30:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:32:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:34:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:36:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:38:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:40:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:42:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:44:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:46:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:48:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:50:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:52:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:54:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:56:00.000', 1);
INSERT INTO table1 values('2013-01-02 02:58:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:00:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:02:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:04:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:06:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:08:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:10:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:12:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:14:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:16:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:18:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:20:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:22:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:24:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:26:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:28:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:30:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:32:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:34:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:36:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:38:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:40:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:42:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:44:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:46:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:48:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:50:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:52:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:54:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:56:00.000', 1);
INSERT INTO table1 values('2013-01-02 03:58:00.000', 1);
INSERT INTO table1 values('2013-01-02 04:00:00.000', 1);

SELECT 
    DATEADD(minute, AP.FiveMinutesPeriod * 15, '2010-01-01T00:00:00') AS Period,
    AP.AvgValue
FROM
     (SELECT
        P.FiveMinutesPeriod,
        SUM(P.Val) AS AvgValue
    FROM
       (SELECT 
           datediff(minute, '2010-01-02T00:00:00', T.DateTime)/15 AS FiveMinutesPeriod,
            T.Val
        FROM table1 T) AS P
    GROUP BY P.FiveMinutesPeriod) AP


run \| edit \| history \| help	0