SQL Server 2022

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

drop table if exists myData
CREATE TABLE myData (Week date, Unit varchar(1), Customer int, Activity varchar(10))
INSERT INTO myData VALUES 
('2024-01-15', 'a', 123, 'Install'),
('2024-01-08', 'b', 123, 'Install'),
('2024-01-08', 'c', 123, 'Removal'),
('2024-01-15', 'd', 123, 'Removal'),
('2024-01-15', 'e', 123, 'Install'),
('2024-01-22', 'f', 123, 'Removal'),
('2024-07-22', 'h', 123, 'Install'),
('2024-05-27', 'g', 123, 'Removal'),
('2024-06-16', 'i', 123, 'Removal'),
('2024-08-01', 'j', 123, 'Install')
10 rows affected

Select 
Removals.Week as Removal_Week
, Removals.Unit as Removal_Unit
, Removals.Customer as Removal_Customer
, Removals.Activity as Removal_Activity
, case when Installs.Week is NULL then 'True Removal'
  else 'Replacement Removal'
  end as Removal_Type
, case when Installs.Week is NULL then 'No Install within 8 weeks afterwards.'
  else 'Belongs to Unit ' + Installs.Unit
  end as Removal_Explanation
, Installs.Week as Install_Week
, Installs.Unit as Install_Unit
, Installs.Customer as Install_Customer
, Installs.Activity as Install_Activity
, case when Removals.Week is NULL then 'True Install'
  else 'Replacement Install'
  end as Install_Type
, case when Removals.Week is NULL then 'No Removal within 8 weeks before.'
  else 'Belongs to Unit ' + Removals.Unit
  end as Install_Explanation
into #Installs_Mapped_To_Removal
From (select * From myData where Activity = 'Removal') as Removals
Full Outer Join (select * From myData where Activity = 'Install') as Installs
on Removals.Customer = Installs.Customer 
and Removals.Week < Installs.Week
and Removals.Week >= dateadd(ww, -8, Installs.Week)
8 rows affected

Select Removal_Week as Week
, Removal_Unit as Unit 
, Removal_Customer as Customer
, Removal_Activity as Activity
, Removal_Type as Type
, Removal_Explanation as Explanation
into #Removals
From #Installs_Mapped_To_Removal
where Removal_Week is not NULL

Select Install_Week as Week
, Install_Unit as Unit 
, Install_Customer as Customer
, Install_Activity as Activity
, Install_Type as Type
, Install_Explanation as Explanation
into #Installs
From #Installs_Mapped_To_Removal
where Install_Week is not NULL
13 rows affected

Select *
From #Removals
Union all 
Select *
From #Installs


Week
Unit
Customer
Activity
Type
Explanation


2024-01-08
c
123
Removal
Replacement Removal
Belongs to Unit a

2024-01-08
c
123
Removal
Replacement Removal
Belongs to Unit e

2024-05-27
g
123
Removal
Replacement Removal
Belongs to Unit h

2024-06-16
i
123
Removal
Replacement Removal
Belongs to Unit h

2024-06-16
i
123
Removal
Replacement Removal
Belongs to Unit j

2024-01-15
d
123
Removal
True Removal
No Install within 8 weeks afterwards.

2024-01-22
f
123
Removal
True Removal
No Install within 8 weeks afterwards.

2024-01-15
a
123
Install
Replacement Install
Belongs to Unit c

2024-01-08
b
123
Install
True Install
No Removal within 8 weeks before.

2024-01-15
e
123
Install
Replacement Install
Belongs to Unit c

2024-07-22
h
123
Install
Replacement Install
Belongs to Unit g

2024-07-22
h
123
Install
Replacement Install
Belongs to Unit i

2024-08-01
j
123
Install
Replacement Install
Belongs to Unit i

Week	Unit	Customer	Activity	Type	Explanation
2024-01-08	c	123	Removal	Replacement Removal	Belongs to Unit a
2024-01-08	c	123	Removal	Replacement Removal	Belongs to Unit e
2024-05-27	g	123	Removal	Replacement Removal	Belongs to Unit h
2024-06-16	i	123	Removal	Replacement Removal	Belongs to Unit h
2024-06-16	i	123	Removal	Replacement Removal	Belongs to Unit j
2024-01-15	d	123	Removal	True Removal	No Install within 8 weeks afterwards.
2024-01-22	f	123	Removal	True Removal	No Install within 8 weeks afterwards.
2024-01-15	a	123	Install	Replacement Install	Belongs to Unit c
2024-01-08	b	123	Install	True Install	No Removal within 8 weeks before.
2024-01-15	e	123	Install	Replacement Install	Belongs to Unit c
2024-07-22	h	123	Install	Replacement Install	Belongs to Unit g
2024-07-22	h	123	Install	Replacement Install	Belongs to Unit i
2024-08-01	j	123	Install	Replacement Install	Belongs to Unit i