SQL Server : group by datefield, sort by time and day

Question

I have a result set, which consists of a datefield and value column.

The date column can be a range from week, month, several months to years. In the format YYYY-mm-dd hh:mm:ss.ssss (2015-03-01 00:00:00.000 for example)

The value column is a simple decimal.

I am having trouble getting the results to group the way I want.

From the query below you may be able to see what I am trying to achieve. I am selecting from my results set, the relative month, hourslot, day and then value.

SELECT 
    CONVERT(VARCHAR(2), datefield, 108) AS hourSlot,
    SUM(value) AS hourTotal,
    DATEPART(day,datefield) as day,
    CASE CONVERT(VARCHAR(2), datefield, 108)
       WHEN @stationStartTime
          THEN 1
          ELSE 0
    END AS sortOrder
FROM  
    @hourTotalTemp
WHERE 
    DATEPART(MM, datefield) = 3
GROUP BY 
    DATEPART(day,datefield)
ORDER BY 
    day, sortOrder, CONVERT(VARCHAR(2), datefield, 108)

As you may be able to see.

I need the results to be grouped by the hour. And then sorted by start time of the station, but also the date of the month.

EDIT:

As above, the end result for the query.

Would be for it to return the hour slot, starting from the defined "start time", the relative day(s) for the month (1,2,3,4,5,6,7th etc), and then the value for those days.

Result set for @hourtotalTemp

datefield               value
------------------------------    
2015-02-01 00:00:00.000 0
2015-02-01 01:00:00.000 0
2015-02-01 02:00:00.000 0
2015-02-01 03:00:00.000 0
2015-02-01 04:00:00.000 0
2015-02-01 05:00:00.000 0

Edit

This is what I would like to achieve with the query. The hours along the top, day on the left. And data in the middle.

1. [date has no display format.](http://stackoverflow.com/questions/30032915/how-to-cast-the-datetime-to-time/30033028#30033028) 2. Please edit your question to include some sample data (preferably ddl+dml) and desired output. — Zohar Peled, Sep 07 '15 at 14:37
@ZoharPeled thanks for the input. I have amended as requested. — dannyb2100, Sep 07 '15 at 15:23

score 0 · Answer 1 · answered Sep 07 '15 at 14:45

Why not do the grouping in a subquery, then just do your sorting on the outer query? Something like this...

SELECT *
from (select CONVERT(VARCHAR(2), datefield, 108) AS hourSlot,
       SUM(value) AS hourTotal,
      DATEPART(day,datefield) as day,
       CASE CONVERT(VARCHAR(2), datefield, 108)
           WHEN @stationStartTime
           THEN 1
           ELSE 0
       END AS sortOrder
FROM @hourTotalTemp
WHERE DATEPART(MM, datefield) = 3
GROUP BY DATEPART(day,datefield) ) tbl
ORDER BY day, sortOrder, hourslot

score 0 · Answer 2 · edited May 23 '17 at 11:58

This will give you an "rounded to the hour" date field:

 dateadd(hour,datediff(hour,0,field),0)

You can use this to group by and order by. This is often easier if you use it in a sub-query since the result is a date field eg

SELECT 
    -- all the stuff you need using hour_date_field
FROM
    (SELECT *, 
       dateadd(hour,datediff(hour,0,datefield),0) as hour_date_field) X
     FROM table) X 
WHERE DATEPART(MM, hour_date_field) = 3
GROUP BY hour_date_field
ORDER BY hour_date_field

Hat Tip / More info - this question (and answer) : Floor a date in SQL server

SQL Server : group by datefield, sort by time and day

2 Answers2