Create Unique ID for Groups of Records

user_13262679 0
Viewed 95

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)
sql
1 Answers

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
user_40011770
Related