Query to categorize rows based on a "time" column without using a CASE expression

There is a ProductTT table as you can see below:

[dbo].[ProductTT] (ID int , Product Varchar(50) , Time Int)

...which contains the following rows:

1   XX   0030
2   UY   0354
3   YY   0517
4   ZZ   0712
5   WW   0415
6   GG   1112
7   MM   1030
8   HH   0913

Note: The format of the data in time column is hh:mm so 0030 is 00:30.

I want to write a query to categorize the rows based on their time value. I need to have 4 categories like this:

category1   00 to 03
category2   03 to 06
category3   06 to 09
category4   09 to 12

I need to see how many rows pertain to each category.

My attempt so far

What I've written so far is like this:

With CTE
AS (select ID,
       product,
       [time],
       Case
         When left(time,2)>=00 and left(time,2)=03 and left(time,2)=06 and left(time,2)=09 and left(time,2)<=12  then  'group4' End AS groupID
from [dbo].[ProductTT]

)
 select groupid,count(*) as recordcount
 from cte
 group by groupid

My question

That query works fine but I just want to know whether there are better ways to write this query and avoid using a CASE expression.

You stored Time as an int but then displayed it as a string (with leading zeros). Those don't get stored, so in order to perform calculations that need to handle the leading zeros, you need to convert to a string first (your current query doesn't do this, so either your query doesn't work, or that table structure is not accurate). Since this is a linear calculation (groups of 3), you can simplify away the CASE expression by simply dividing the first two digits in the time by 3 (and thanks to SQL Server's integer division, the remainder gets discarded, and we add 1 to go from 0-3 to 1-4). Of course, there is an exception, because you want 12 PM to be in group 4, not group 5. With a CASE expression this could just be left to the ELSE clause, but if you eliminate CASE, you will have to deal with that exception explicitly - that's all the COALESCE/NULLIF stuff at the end.

;WITH x AS 
(
  SELECT ID, Product, [Time] = RIGHT('000'+CONVERT(varchar(4),[Time]),4) 
  FROM dbo.ProductTT
), y AS 
(
  SELECT ID, Product, [Time], h = CONVERT(char(2),[Time]) 
  FROM x
)
SELECT ID, Product, [Time], 
  [GroupID] = 'group' + CONVERT(char(1),h/3+1-COALESCE(NULLIF(h%11,1)-h%11,1))
FROM y;

Results:

ID Product Time GroupID
-- ------- ---- -------
1 XX 0030 group1
2 UY 0354 group2
3 YY 0517 group2
4 ZZ 0712 group3
5 WW 0415 group2
6 GG 1112 group4
7 MM 1030 group4
8 HH 0913 group4


I strongly recommend you use the actual time data type, as that is what it was designed for. Then you can use DATEPART(HOUR( in your calculations instead of messy string manipulation, the query above is less complex and, as a bonus, you get built-in validation, to avoid invalid times like 1369 and 9997. Or if the leading zeros are important but you don't care about validation, use char(4) instead of int.

I also think you need to handle the case where an event happens in the afternoon.

And FWIW I am not sure why you don't want to use a CASE expression here. It's a few more characters, sure, but it's a lot more clear what the query is actually doing. Code that is self-documenting is much more valuable than code that is slightly shorter. This is simpler IMHO, and would be even simpler if you used the right data types:

;WITH x AS 
(
  SELECT ID, Product, [Time] = RIGHT('000'+CONVERT(varchar(4),[Time]),4) 
  FROM dbo.ProductTT
)
SELECT ID, Product, [Time],
  GroupID = 'group' + CASE CONVERT(char(2),[Time])/3
    WHEN 0 THEN '1'
    WHEN 1 THEN '2'
    WHEN 2 THEN '3'
    ELSE '4' END 
FROM x;

StackExchange Database Administrators Q#237015, answer score: 4