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

Question

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  then  'group1'
         when left(time,2)>=03 and left(time,2)< 06  then  'group2'
         when left(time,2)>=06 and left(time,2)< 09  then  'group3'
         when 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.

Aaron Bertrand · Accepted Answer · 2019-04-30T11:33:27.030

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;

Hannah Vernon · Answer 2 · 2019-04-30T19:23:06.650

I'd use a numbers table to categorize the values in dbo.ProductTT.

I've created a simple MCVE¹ to show how this works. FYI, in future, it would be great if you'd provide code like this when asking a question. It helps everyone.

USE tempdb;
IF OBJECT_ID(N'dbo.ProductTT', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.ProductTT;
END
CREATE TABLE dbo.ProductTT
(
    ID int NOT NULL PRIMARY KEY CLUSTERED
    , Product varchar(50) NOT NULL
    , CreateTime int NOT NULL
    , FormattedCreateTime AS RIGHT('0000' + CONVERT(varchar(4), CreateTime), 4)
);

INSERT INTO dbo.ProductTT
VALUES (1, 'XX', 0030)
    , (2, 'UY', 0354)
    , (3, 'YY', 0517)
    , (4, 'ZZ', 0712)
    , (5, 'WW', 0415)
    , (6, 'GG', 1112)
    , (7, 'MM', 1030)
    , (8, 'HH', 0913)
    , (9, 'H1', 1230)
    , (10, 'H2', 1359)
    , (11, 'H3', 2359);

IF OBJECT_ID(N'dbo.TimeGroups', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.TimeGroups;
END
CREATE TABLE dbo.TimeGroups
(
    TimeGroupStart int NOT NULL
    , TimeGroupEnd int NOT NULL
    , TimeGroupName varchar(9) NOT NULL
    , PRIMARY KEY CLUSTERED (TimeGroupStart, TimeGroupEnd)
);

INSERT INTO dbo.TimeGroups (TimeGroupStart, TimeGroupEnd, TimeGroupName)
VALUES (0,  3,  '00 to 03')
     , (3,  6,  '03 to 06')
     , (6,  9,  '06 to 09')
     , (9,  12, '09 to 12')
     , (12, 15, '12 to 15')
     , (15, 18, '15 to 18')
     , (18, 21, '18 to 21')
     , (21, 24, '21 to 24');

The "numbers table" in the code above is called "TimeGroups".

To get the desired output, you simply join the two tables together, as in:

SELECT tg.TimeGroupName
    , TimeGroupCount = COUNT(1)
FROM dbo.ProductTT tt
    INNER JOIN dbo.TimeGroups tg ON (tt.CreateTime / 100) >= tg.TimeGroupStart
        AND (tt.CreateTime / 100) < tg.TimeGroupEnd
GROUP BY tg.TimeGroupName
ORDER BY tg.TimeGroupName;

The output looks like:

╔═══════════════╦════════════════╗
║ TimeGroupName ║ TimeGroupCount ║
╠═══════════════╬════════════════╣
║ 00 to 03      ║              1 ║
║ 03 to 06      ║              3 ║
║ 06 to 09      ║              1 ║
║ 09 to 12      ║              3 ║
║ 12 to 15      ║              2 ║
║ 21 to 24      ║              1 ║
╚═══════════════╩════════════════╝

Note that the JOIN clause in the above query specifies the range as greater-than-or-equal to the start of the category, and less-than the end of the category. If you used less-than-or-equal-to for the end of the range, you'd have ProductTT rows showing up in multiple categories, which is clearly incorrect.

You can see how the join works with this simple query:

SELECT tt.*
    , Category = tt.CreateTime / 100
FROM dbo.ProductTT tt

The output looks like:

╔════╦═════════╦════════════╦═════════════════════╦══════════╗
║ ID ║ Product ║ CreateTime ║ FormattedCreateTime ║ Category ║
╠════╬═════════╬════════════╬═════════════════════╬══════════╣
║  1 ║ XX      ║         30 ║                0030 ║        0 ║
║  2 ║ UY      ║        354 ║                0354 ║        3 ║
║  3 ║ YY      ║        517 ║                0517 ║        5 ║
║  4 ║ ZZ      ║        712 ║                0712 ║        7 ║
║  5 ║ WW      ║        415 ║                0415 ║        4 ║
║  6 ║ GG      ║       1112 ║                1112 ║       11 ║
║  7 ║ MM      ║       1030 ║                1030 ║       10 ║
║  8 ║ HH      ║        913 ║                0913 ║        9 ║
║  9 ║ H1      ║       1230 ║                1230 ║       12 ║
║ 10 ║ H2      ║       1359 ║                1359 ║       13 ║
║ 11 ║ H3      ║       2359 ║                2359 ║       23 ║
╚════╩═════════╩════════════╩═════════════════════╩══════════╝

^{1 - I own the website pointed to in that link}

score 2 · Answer 3 · edited Oct 29 '20 at 18:46

You could convert the hour part of the time string and divide it by three. The integer of this division plus 1 is equal to your group number.

(00/3) + 1 = 1

(01/3) + 1 = 1

(02/3) + 1 = 1

(03/3) + 1 = 2

(04/3) + 1 = 2

...

In that way you will no longer need the case.

To explain a little further; you use the case because you want to know if the time belongs to the group1, group2 and so on. One way to avoid the case is to figure out what group the time belongs using the formulas that I give to you. You can calculate the groupId field using that formula: "group"&((to_int(to_int(left(time,2)))/3)+1). I do not know the function to convert string to int in your database so a used to_int in the example.

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

My attempt so far

My question

3 Answers3