sql / linq to entities: group on group of hours

Question

Hi,

i have a table with snow data which i get delivered per hour. so for instance at between 0am and 1 am 1 cm snow will fall, between 1 am and 2 am 3 cm snow will fall, between 2 am and 3 am 0 cm snow will fall, between 3 am and 4 am 2 cm snow will fall etc. so the table has a Snowdate column (datetime), a Snowdate hour column (int) and a snowfall column (int) now i want to present the data grouped by groups of 6 hours (0-5, 6-11, 12-17 and 18-23), so when between 0 am and 6 am i have 6 records (1cm, 3c, 0cm, 2 cm, 2cm, 0cm) i want to show one row with the number 8, and so on for the rest of the day. For every hour a day there will be a record in the db, so always 24 records a day

A pure sql solution will be ok (a view or so) or a linq to entities will be ok too.

Michel

Answer 1

Group by the hour / 6 (using integer arithmetic) and select for grouping the amount column. Select into a new object, the Key * 6 and Sum() the resulting grouping for the total for that "hour". The "hour" will be the first hour in each range.

var query = db.SnowRecords.GroupBy( s => s.SnowHour / 6, a => a.SnowFall )
                          .Select( g => new {
                            Hour = g.Key * 6,
                            Amount = g.Sum()
                           });

You don't say if you need to group by date as well, but if you do, then this would become the inner query on the records grouped by date.

var query = db.SnowRecords.GroupBy( s => s.SnowDate.Date )
                          .Select( g => new {
                                Date = g.Key,
                                HourlySnowFall = g.GroupBy( s => s.SnowHour / 6, a => a.SnowFall )
                                                  .Select( sg => new {
                                                      Hour = sg.Key * 6,
                                                      Amount = sg.Sum()
                                                   })
                           });

Answer 2

If i understand correct, try something like this

DECLARE @Table TABLE(
     SnowDate DATETIME,
     SnowHour INT,
     SnowFall INT
)

INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 0, 1
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 1, 3
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 2, 0
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 3, 2
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 4, 2
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 5, 0
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 6, 10
INSERT INTO @Table (SnowDate,SnowHour,SnowFall) SELECT '10 Sep 2009', 7, 10


SELECT  SnowDate,
     CAST(FLOOR((SnowHour) / 6.) * 6 AS VARCHAR(4)) + ' TO ' + CAST((FLOOR((SnowHour) / 6.) + 1) * 6 - 1 AS VARCHAR(4)),
     SUM(SnowFall) AS Total
FROM    @Table
GROUP BY    SnowDate,
      FLOOR((SnowHour) / 6.)