SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select @@version;


(No column name)


Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) 
 Apr 11 2022 16:24:07 
 Copyright (C) 2019 Microsoft Corporation
 Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)



CREATE TABLE random_table

  (
  ID VarChar(50),
date DATETIME
 );

 INSERT INTO random_table
 VALUES
  ('AAA','2021-05-14'),
('AAA','2021-06-02'),
('AAA','2021-07-09'),
('BBB', '2021-05-25'),
('CCC', '2020-12-02'),
('CCC','2020-12-06'),
('CCC','2020-12-10'),
('CCC','2020-12-14'),
('CCC','2020-12-18'),
('CCC','2020-12-22'),
('CCC','2020-12-26'),
('CCC','2020-12-30'),
('CCC','2021-01-03'),
('DDD','2021-01-07'),
('DDD','2021-01-11');
15 rows affected

SELECT  
   CASE WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [date])=1 THEN 1 
        ELSE DATEPART(WK, (DATE) ) - DATEPART(WK, FIRST_VALUE([DATE]) OVER (PARTITION BY ID ORDER BY [date])) END  PD,
   ID,
   CONVERT(VARCHAR(10), [date],120)
FROM random_table rt 
ORDER BY ID,[date]


PD
ID
(No column name)


1
AAA
2021-05-14

3
AAA
2021-06-02

8
AAA
2021-07-09

1
BBB
2021-05-25

1
CCC
2020-12-02

1
CCC
2020-12-06

1
CCC
2020-12-10

2
CCC
2020-12-14

2
CCC
2020-12-18

3
CCC
2020-12-22

3
CCC
2020-12-26

4
CCC
2020-12-30

-47
CCC
2021-01-03

1
DDD
2021-01-07

1
DDD
2021-01-11

PD	ID	(No column name)
1	AAA	2021-05-14
3	AAA	2021-06-02
8	AAA	2021-07-09
1	BBB	2021-05-25
1	CCC	2020-12-02
1	CCC	2020-12-06
1	CCC	2020-12-10
2	CCC	2020-12-14
2	CCC	2020-12-18
3	CCC	2020-12-22
3	CCC	2020-12-26
4	CCC	2020-12-30
-47	CCC	2021-01-03
1	DDD	2021-01-07
1	DDD	2021-01-11