Jtdcftul

Hope you're doing well.
There is table as you can see below:

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

and records in the table:

1   XX   0030

2   UY   0354

3   YY   0517

4   ZZ   0712

5   WW   0415

6   GG   1112

7   MM   1030

8   HH   0913

format of the data in [time] column is hh:mm so 0030 is 00:30.
I want to write a query to categorize the records based on their time . 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 records do I have in each category.
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

the query works fine but I just want to know whether there are better ways to write this query and avoid using Case statement.
Thanks in advance

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;

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.