Add a group number

Transact-SQL
1

Hello, I have a table that I would like to add a column as a group number. The group number will be based on date, so any record with the same date will be in the same group. Here is an example of what I want after my update.

DECLARE @Dates TABLE (MyDateTime DATETIME, MyGroup INT); INSERT INTO @Dates (MyDateTime, MyGroup) VALUES ('2014-12-22 11:37:51.000',1), ('2014-12-22 11:39:17.000',1), ('2014-12-29 14:26:42.000',2), ('2014-12-29 14:28:12.000',2), ('2014-12-30 18:27:04.000',3), ('2014-12-30 18:28:14.000',3), ('2015-01-09 09:01:19.000',4), ('2015-01-09 09:01:19.000',4);

I have MyDateTime and want to populate MyGroup. The actual data have different number of date/time values.
Since this is groups I do not know if RANK can be used (I did not think so).

Thank you,
DJJ

2

DENSE_RANK() over(order by convert(date, MyDateTime))

3

Thank you @khtan!

Now to code so I can update a table with it. :slight_smile: Not a big problem, but my quick check tells me I cannot use the function directly in an update. :pensive: so I think I will end up with a CTE.

4

yes. You will need a CTE or derived table for update