SQL query - Find daily MIN value from hourly sums

Question

Let's cut to the chase. I have a table which looks like this one (using SQL Server 2014):

DEMO: http://sqlfiddle.com/#!6/75f4a/1/0

CREATE TABLE TAB (
    DT datetime,
    VALUE float
);

INSERT INTO TAB VALUES
('2015-05-01 06:00:00', 12),
('2015-05-01 06:20:00', 10),
('2015-05-01 06:40:00', 11),
('2015-05-01 07:00:00', 14),
('2015-05-01 07:20:00', 15),
('2015-05-01 07:40:00', 13),
('2015-05-01 08:00:00', 10),
('2015-05-01 08:20:00', 9),
('2015-05-01 08:40:00', 5),

('2015-05-02 06:00:00', 19),
('2015-05-02 06:20:00', 7),
('2015-05-02 06:40:00', 11),
('2015-05-02 07:00:00', 9),
('2015-05-02 07:20:00', 7),
('2015-05-02 07:40:00', 6),
('2015-05-02 08:00:00', 10),
('2015-05-02 08:20:00', 19),
('2015-05-02 08:40:00', 15),

('2015-05-03 06:00:00', 8),
('2015-05-03 06:20:00', 8),
('2015-05-03 06:40:00', 8),
('2015-05-03 07:00:00', 21),
('2015-05-03 07:20:00', 12),
('2015-05-03 07:40:00', 7),
('2015-05-03 08:00:00', 10),
('2015-05-03 08:20:00', 4),
('2015-05-03 08:40:00', 10)

I need to:

sum values hourly
select the smallest 'hourly sum' for each day
select hour for which that sum occurred

In other words, I want to have a table which looks like this:

DATE |  SUM VAL | ON HOUR
--------------------------
2015-03-01 | 24 | 8:00 
2015-03-02 | 22 | 7:00 
2015-03-03 | 24 | 6:00

First two points a very easy (check out sqlfiddle). I have a problem with the third one. I can't just like that select Datepart(HOUR, DT) bacause it has to be aggregated. I was trying to use JOINS and WHERE clause, but with no success (some values may occur in table more than once, which thrown an error).

I'm kinda new with SQL and I got stuck. Need your help SO! :)

In your sample data two different hours have the same sum of value (24) for the 3rd. Do you want both records returned or just one, and if so, which one? — jpw, Mar 26 '15 at 20:15
@jpw I did this on purpose. I want to select only one row - with the "smaller" hour. I see a lot of different solutions, gonna try them tommorow at work on table with real data. — rafakob, Mar 26 '15 at 20:57

score 2 · Answer 1 · answered Mar 26 '15 at 16:57

DECLARE @TAB TABLE
    (
      DT DATETIME ,
      VALUE FLOAT
    );

INSERT  INTO @TAB
VALUES  ( '2015-05-01 06:00:00', 12 ),
        ( '2015-05-01 06:20:00', 10 ),
        ( '2015-05-01 06:40:00', 11 ),
        ( '2015-05-01 07:00:00', 14 ),
        ( '2015-05-01 07:20:00', 15 ),
        ( '2015-05-01 07:40:00', 13 ),
        ( '2015-05-01 08:00:00', 10 ),
        ( '2015-05-01 08:20:00', 9 ),
        ( '2015-05-01 08:40:00', 5 ),
        ( '2015-05-02 06:00:00', 19 ),
        ( '2015-05-02 06:20:00', 7 ),
        ( '2015-05-02 06:40:00', 11 ),
        ( '2015-05-02 07:00:00', 9 ),
        ( '2015-05-02 07:20:00', 7 ),
        ( '2015-05-02 07:40:00', 6 ),
        ( '2015-05-02 08:00:00', 10 ),
        ( '2015-05-02 08:20:00', 19 ),
        ( '2015-05-02 08:40:00', 15 ),
        ( '2015-05-03 06:00:00', 8 ),
        ( '2015-05-03 06:20:00', 8 ),
        ( '2015-05-03 06:40:00', 8 ),
        ( '2015-05-03 07:00:00', 21 ),
        ( '2015-05-03 07:20:00', 12 ),
        ( '2015-05-03 07:40:00', 7 ),
        ( '2015-05-03 08:00:00', 10 ),
        ( '2015-05-03 08:20:00', 4 ),
        ( '2015-05-03 08:40:00', 10 );
WITH    cteh
          AS ( SELECT   DT ,
                        CAST(dt AS DATE) AS D ,
                        SUM(VALUE) OVER ( PARTITION BY CAST(dt AS DATE),
                                          DATEPART(hh, DT) ) AS S
               FROM     @TAB
             ),
        ctef
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY D ORDER BY S ) AS rn
               FROM     cteh
             )
    SELECT  D ,
            S ,
            CAST(DT AS TIME) AS H
    FROM    ctef
    WHERE   rn = 1

Output:

D           S   H
2015-05-01  24  08:00:00.0000000
2015-05-02  22  07:00:00.0000000
2015-05-03  24  06:00:00.0000000

jpw · Accepted Answer · 2015-03-26T20:34:19.923

One way is to use the set with minimum hourly values as a derived table and join against that. I would do something like this:

;WITH CTE AS (
    SELECT Cast(Format(DT, 'yyyy-MM-dd HH:00') AS datetime) AS DT, SUM(VALUE) AS VAL
    FROM TAB
    GROUP BY Format(DT, 'yyyy-MM-dd HH:00')
) 

SELECT b.dt "Date", val "sum val", cast(min(a.dt) as time) "on hour"
FROM cte a JOIN (
    SELECT Format(DT,'yyyy-MM-dd') AS DT, MIN(VAL) AS DAILY_MIN 
    FROM cte HOURLY
    GROUP BY Format(DT,'yyyy-MM-dd')
) b ON CAST(a.DT AS DATE) = b.DT and a.VAL = b.DAILY_MIN
GROUP BY b.DT, a.VAL

This would get:

Date        sum val on hour
2015-05-01  24      08:00:00.0000000
2015-05-02  22      07:00:00.0000000
2015-05-03  24      06:00:00.0000000

I used min() for the time part as your sample data has the same low value for two separate hour for the 3rd. If you want both then remove the min function from the outer select and the group by. Then you would get:

Date        sum val on hour
2015-05-01  24      08:00:00.0000000
2015-05-02  22      07:00:00.0000000
2015-05-03  24      06:00:00.0000000
2015-05-03  24      08:00:00.0000000

I'm sure it can be improved, but you should get the idea.

Thanks, I've adjusted the code a little bit, tested and it works great! :) I must say that I like those solutions with CTE, didn't know u can do such things in SQL. — rafakob, Mar 27 '15 at 09:01

score 1 · Answer 3 · answered Mar 26 '15 at 17:19

Here's a method that uses a Temp Table (as opposed to the CTE's in the other solutions) to store calculated values and then filters the results to give you your desired output:

-- INSERT CALCULATED GROUPED VALUES INTO TEMP TABLE
SELECT  CONVERT(DATE, DT) AS DateVal ,
        SUM(VALUE) AS SumVal ,
        DATEPART(HOUR, CONVERT(TIME, DT)) AS HourVal
INTO    #TEMP_CALC
FROM    TAB
GROUP BY CONVERT(DATE, DT) , DATEPART(HOUR, CONVERT(TIME, DT))

-- TAKE THE RELEVANT ROWS
SELECT  t.DateVal ,
        MIN(t.SumVal) AS SumVal ,
        ( SELECT TOP 1
                    HourVal
          FROM      #TEMP_CALC t2
          WHERE     t2.DateVal = t.DateVal
                    AND t2.SumVal = MIN(t.SumVal)
        ) AS MinHour
FROM    #TEMP_CALC t
GROUP BY t.DateVal
ORDER BY DateVal

If you use a common table expression, you can forgo the need to create the temp table and complete the call in one command — Erik Blessman, Mar 26 '15 at 18:19

score 0 · Answer 4 · edited May 23 '17 at 12:22

You can use DATEDIFF to get the time spans from any starting point in time (1990-1-1 in this sample) in hours and days. The use that spans to group and order, and finally use DATEADD with the same starting point to rebuild it:

WITH dates AS (
  SELECT CAST(DT AS DATETIME) AS Date, -- cast the value to date
  value FROM dbo.TAB AS T
),
ddh AS (SELECT 
    date,
    DATEDIFF(DAY, '1990-1-1', date) AS daySpan,    -- days span
    DATEDIFF(HOUR, '1990-1-1', date) AS hourSpan,  -- hours span
    value
    FROM dates
),
ddhv AS ( SELECT
    daySpan,
    hourSpan,
    SUM(value) AS sumValues    -- sum...
    FROM ddh
    group BY daySpan, hourSpan -- ...grouped by day & hour
),
ddhvr AS ( SELECT
    daySpan,
    hourSpan,
    sumValues,
    -- number rows by hourly sum of the value
    ROW_NUMBER() OVER (PARTITION BY daySpan ORDER BY sumValues) AS row
FROM ddhv
)
SELECT
    DATEADD(HOUR, hourSpan, '1990-1-1') AS DayHour, -- rebuild the date/hour
    sumValues
FROM ddhvr
WHERE row = 1 -- take only the first occurrence for each day

This query has the advantage that you can change the periods, and the starting point easyly. For example you can make your days starts at 6:30 AM instead of at 00:00,so that the compared periods are 6:30 to 7:30, 7:30 to 8:30 and do on. And you can also change the grouping unit, for example, instead of 1 hour it could be half an hour, or 5 minutes or 2 hours. If you need to do do, please, see this SO answer. There you'll see how you can make the grouping by different periods, and get back the period staring point. It's just some simple maths.

score 0 · Answer 5 · answered Mar 26 '15 at 18:03

I tested my against your fiddle:

with agg as (
    select cast(dt as date) as dt, datepart(hh, dt) as hr, sum(VALUE) as sum_val
    from TAB
    group by cast(dt as date), datepart(hh, dt)
)
select
    dt, min(sum_val) as "SUM VAL",
    (
        select cast(hr as varchar(2)) + ':00' from agg as agg2
        where agg2.dt = agg.dt and not exists (
            /* select earliest in case of ties */
            select 1 from agg as agg3
            where agg3.dt = agg2.dt and agg3.sum_val >= agg3.sum_val and agg3.hr > agg2.hr
        )
    ) as "ON HOUR"
from agg
group by dt;

SQL query - Find daily MIN value from hourly sums

5 Answers5