Create Unique ID for Groups of Records

I spent a good amount of time searching for a solution on here and google but came up empty. I thought dense_rank() might work but I can’t get it to do what I’m needing. I’m using SSMS 18. What I’m trying to do is assign a unique ID to groups of records that I have partitioned with row_number().

The data looks like this:

RN	Client_ID	Date
1	xxxx	2022-08-23
2	xxxx	2022-08-23
3	xxxx	2022-08-23
1	xxxx	2022-08-25
2	xxxx	2022-08-25
1	yyyy	2022-06-10
2	yyyy	2022-06-10
1	gggg	2021-05-06
2	gggg	2021-05-06
3	gggg	2021-05-06
4	gggg	2021-05-06

So each group of records now needs to have a unique ID attributed to them. So it would look something like this:

UnqID	RN	Client_ID	Date
0001	1	xxxx	2022-08-23
0001	2	xxxx	2022-08-23
0001	3	xxxx	2022-08-23
0002	1	xxxx	2022-08-25
0002	2	xxxx	2022-08-25
0003	1	yyyy	2022-06-10
0003	2	yyyy	2022-06-10
0004	1	gggg	2021-05-06
0004	2	gggg	2021-05-06
0004	3	gggg	2021-05-06
0004	4	gggg	2021-05-06

Thanks in advance for any help on this.

row_number() 
over (partition by a.PAT_ID, cast(a.EFFECTIVE_DATE_DT as date) 
order by case
when a.[EncType_C] = '1000' then 1
when a.[EncType_C] = '101'  then 2                   
when a.[EncType_C] = '3' 
     and (dpt.DeptNm not like '%X-RAY%' and dpt.DeptNm != 'VCM LAB') then 3
when a.[EncType_C] = '50'   then 4
when a.[EncType_C] = '3' 
     and (dpt.DeptNm like '%X-RAY%' or dpt.DeptNm = 'VCM LAB') then 5
else  6
end,
case
when a.[DeptID] = 100101024 then 1
when a.[DeptID] = 100101055 then 2
else 0
end)

Answer

try using a common table entry cte and groupby

declare @tmp as table(RN int, Client_ID varchar(10), Date   Date);
insert into @tmp
values
(1,'xxxx','2022-08-23'),
(2,'xxxx','2022-08-23'),
(3,'xxxx','2022-08-23'),
(1,'xxxx','2022-08-25'),
(2,'xxxx','2022-08-25'),
(1,'yyyy','2022-06-10'),
(2,'yyyy','2022-06-10'),
(1,'gggg','2021-05-06'),
(2,'gggg','2021-05-06'),
(3,'gggg','2021-05-06'),
(4,'gggg','2021-05-06');

with cte
as
(
select
distinct
Client_ID,
Date,
Count(Client_ID)  over(order by Client_ID,Date) RowID
from @tmp
group by
Client_ID,
Date
)
--select * from cte
select Client_ID, Date,
Lookup.UnqID
from @tmp tmp
cross apply
(
select RowID UnqID from cte 
where tmp.Client_ID=cte.Client_ID and tmp.Date=cte.Date
)Lookup

output

Client_ID   Date    UnqID
gggg    2021-05-06  1
gggg    2021-05-06  1
gggg    2021-05-06  1
gggg    2021-05-06  1
xxxx    2022-08-23  2
xxxx    2022-08-23  2
xxxx    2022-08-23  2
xxxx    2022-08-25  3
xxxx    2022-08-25  3
yyyy    2022-06-10  4
yyyy    2022-06-10  4