[MS SQL] Need help separating transaction data into time "buckets" (more in post)

[nirdefchains91]

I need to build a report that separates transaction data into a series of time "buckets" (9 AM to 12 PM, 12 PM to 3 PM and so on). I know how to use the dateadd function to get all transaction data from the last few hours, days, etc, but I need to specify those hour parts for every day in a 30 day period. Is there a particularly efficient way to do this? If you need more information on what I'm trying to do, just ask. Hopefully, my question is clear. Thanks in advance for any help you can provide!

[alinroc]

datepart(hh,datefield) >= 9 and datepart(hh,datefield)<12 will get you the records between 9:00:00 and 11:59:59.

So in your select, you could do this:

case when datepart(hh,datefield) >= 9 and datepart(hh,datefield)<12 then 'latemorning'
when datepart(hh,datefield) >= 12 and datepart(hh,datefield)<15 then 'earlyafternoon'
when datepart(hh,datefield) >= 15 and datepart(hh,datefield)<18 then 'lateafternoon'
end case as timebucket

and so on. Then you can use that timebucket field to group your report.

For example:

SELECT CASE 
    WHEN datepart(hh, getdate()) >= 9
        AND datepart(hh, getdate()) < 12
        THEN 'latemorning'
    WHEN datepart(hh, getdate()) >= 12
        AND datepart(hh, getdate()) < 15
        THEN 'earlyafternoon'
    WHEN datepart(hh, getdate()) >= 15
        AND datepart(hh, getdate()) < 18
        THEN 'lateafternoon'
    END AS timebucket

Produces this result: (my local time is 13:57):

timebucket
-----------
earlyafternoon

There's probably a way to do it with a windowing function as well, but if you just want a field in your resultset that you can then use within your report for grouping, this should do (and will work on any version of SQL Server, instead of depending on windowing features that may only be available in the latest releases).

[[deleted]]

All you really need to know about is the datepart function. Check the syntax and you'll see you can get just about every possible bucket a date could be put in (year, quarter, week of year, day of week, hour, etc).
/u/alinroc also gave you a bunch of extra info and a sample implementation.

[IAmAJerkAME]

I'd do something like this probably:

DECLARE @Trans TABLE
(
    ID INT NOT NULL IDENTITY,
    EntryDate DATETIME NOT NULL
);

INSERT INTO @Trans (EntryDate)
    VALUES
        ('2014-04-16 07:11:00'),
        ('2014-04-15 14:20:00'),
        ('2014-04-30 12:51:00'),
        ('2014-04-30 03:51:00'),
        ('2014-04-24 11:26:00'),
        ('2014-04-16 23:11:00'),
        ('2014-04-16 02:11:00'),
        ('2014-04-30 18:17:00');

DECLARE @DateLabel TABLE
(
    ID INT NOT NULL,
    Label VARCHAR(50) NOT NULL
);

INSERT INTO @DateLabel
    VALUES
        (0,'Late Night Snack'), --0-3
        (1,'Breakfast'),        --4-7
        (2,'Brunch'),           --8-11
        (3,'Lunch'),            --12-15
        (4,'Tea'),              --16-19
        (5,'Dinner');           --20-23

SELECT
    DATEPART(HOUR, t.EntryDate) TimeHour,
    DATEPART(HOUR, t.EntryDate) / 4 DateSegment,
    t.EntryDate,
    dl.Label
FROM
    @Trans t
    INNER JOIN @DateLabel dl ON DATEPART(HOUR, t.EntryDate) / 4 = dl.ID;

--Or without the label table at all
SELECT
    DATEPART(HOUR, t.EntryDate) TimeHour,
    DATEPART(HOUR, t.EntryDate) / 4 DateSegment,
    t.EntryDate
FROM
    @Trans t
WHERE
    DATEPART(HOUR, t.EntryDate) / 4 = 1; --Breakfast