How can I create time range grouping in window function SQL-CodePudding

I'm trying to create a grouping using multiple window function on SQL, the objective is to discern between different groups if there are some other groups in the middle. see below table

Part |       time           |    expected result |
a    | 11-29-2022 00:05:00.000  |       1        |
a    | 11-29-2022 00:05:00.010  |       1        |
b    | 11-29-2022 00:06:00.000  |       2        |
c    | 11-29-2022 00:15:00.000  |       3        |
c    | 11-29-2022 00:15:00.000  |       3        |
b    | 11-29-2022 00:40:00.010  |       4        |
b    | 11-29-2022 00:40:00.020  |       4        |
b    | 11-29-2022 00:40:00.020  |       4        |
b    | 11-29-2022 00:40:00.030  |       4        |

I'm doing something like:

Select part, time, count(*) over(Partition by Part order by time )

Lets focus in part "b", first occurrence is at minute 6, after that appears different parts and part b appears again at minute 40 so I need something like a time range to create the grouping

Also notice that sometimes the time is different in milliseconds even if the parts are consecutive (part b), those must belong to the same group. Was trying to use the Rank window function but with 'range between' wasn't able to get that result.

Thanks!

CodePudding user response：

Just another option via dense_rank()

Select * 
      ,NewValue = dense_rank() over (order by convert(varchar(25),[Time],120))
 From YourTable

Results

CodePudding user response：

Please try this sql query.

Select part, time, dense_rank() over(Partition by Part )

Select part, time, dense_rank() over(Partition by Part order by time rows between unbounded preceding and unbounded following  )