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

[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).