CODEWITHSUNDEEP
sql-servert-sqldatetimegroup-byoffset
Bogu
Bogu

Reputation: 83

T- Sql Group by n hours

It's possible to simple group by n hours? I found related questions for minutes but I cant do it with hours.

I need to group some data by 8 hours with offset.

So the first 8 hours are not 00:00 - 08:00 but 22:00 (yesterday) - 06:00 (today).

I grouped this by hour and stuck. How can I do that? Any advice is appreciated.

Upvotes: 1

Views: 341

Answers (2)

Michał Turczyn
Michał Turczyn

Reputation: 37367

I don't know neither data, input nor output (which you should provide), but here's some general way:

assume we have simple table (named SimpleTable) with 2 columns: time of selling product and quantity sold:

SellingTime             | Quantity
2013-01-02 08:43:22.011 | 4
2013-02-12 12:32:12.001 | 14
etc.

Now, we will add ordering number to for each date: 1 for time between 22-6 etc.

SELECT SellingTime, Qunatity,
       CASE WHEN DATEPART(hh, SellingTime) >= 22 AND DATEPART(hh, SellingTime) < 6 THEN 1
            WHEN DATEPART(hh, SellingTime) >= 6 AND DATEPART(hh, SellingTime) < 14 THEN 2
            ELSE 3 END AS NumberOfOrder
FROM SimpleTable

Now, all you need to do is to group by NumberOfOrder and sum Quantity:

SELECT NumberOfOrder, SUM(Quantity) FROM (*)

where in place of * you put first query.

Upvotes: 0

gbn
gbn

Reputation: 432271

You can use the DATEADD/DATEDIFF trick to group by 8 hour intervals.

You need to add an offset though, in this case

  • Add 2 hours to shift 22:30 to 00:30. Or 07:30 back to 09:30
  • Group by the 8 hour interval (0-8, 8-16, 8-24)
  • Subtract 2 to shift the rounded 00:00 back to 22:00, or the rounded 08:00 to 06:00

Example script

DECLARE @foo table (foo smalldatetime, bar int);

INSERT @foo (foo, bar)
VALUES
    ('2017-10-11 21:00', 1),
    ('2017-10-11 22:00', 2),
    ('2017-10-11 23:00', 3),
    ('2017-10-12 01:00', 4),
    ('2017-10-12 03:00', 5),
    ('2017-10-12 05:00', 6),
    ('2017-10-12 07:00', 7),
    ('2017-10-12 08:00', 8);

SELECT 
    SUM(bar), 
    DATEADD(hh, ((DATEDIFF(hh, 0, foo)+2)/8*8)-2, 0)
FROM 
    @foo 
GROUP BY 
    DATEADD(hh, ((DATEDIFF(hh, 0, foo)+2)/8*8)-2, 0);

Upvotes: 2

Related Questions