SQL Server 2019

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

CREATE TABLE [dbo].[samplerfiddata](
    [Item] [nvarchar](50) NOT NULL,
    [Station_Type] [nvarchar](50) NOT NULL,
    [Station_Name] [nvarchar](50) NOT NULL,
    [Timestamp] [datetime2](7) NOT NULL,
    [Trying_to_Create_this_Flag_Column] [nvarchar](50) NOT NULL
) ON [PRIMARY]
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-10T06:30:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-11T14:30:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Washer', N'Washer', CAST(N'2020-10-11T14:45:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-11T15:15:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-11T23:30:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Washer', N'Washer', CAST(N'2020-10-12T00:15:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-12T00:45:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-13T16:00:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Other', N'Decontamination', CAST(N'2020-10-13T16:30:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'A', N'Decontamination', N'Decontamination', CAST(N'2020-10-14T13:30:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Other', N'Decontamination', CAST(N'2020-10-12T08:30:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T14:30:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Washer', N'Washer', CAST(N'2020-10-12T14:45:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T15:15:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-12T18:00:00.0000000' AS DateTime2), N'1')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Washer', N'Washer', CAST(N'2020-10-13T18:15:00.0000000' AS DateTime2), N'0')
;
INSERT [dbo].samplerfiddata ([Item], [Station_Type], [Station_Name], [Timestamp], [Trying_to_Create_this_Flag_Column]) VALUES (N'B', N'Decontamination', N'Decontamination', CAST(N'2020-10-13T19:00:00.0000000' AS DateTime2), N'1')
;
17 rows affected

select * from samplerfiddata


Item
Station_Type
Station_Name
Timestamp
Trying_to_Create_this_Flag_Column


A
Decontamination
Decontamination
2020-10-10 06:30:00.0000000
1

A
Decontamination
Decontamination
2020-10-11 14:30:00.0000000
1

A
Washer
Washer
2020-10-11 14:45:00.0000000
0

A
Decontamination
Decontamination
2020-10-11 15:15:00.0000000
0

A
Other
Decontamination
2020-10-11 23:30:00.0000000
1

A
Washer
Washer
2020-10-12 00:15:00.0000000
0

A
Other
Decontamination
2020-10-12 00:45:00.0000000
0

A
Other
Decontamination
2020-10-13 16:00:00.0000000
1

A
Other
Decontamination
2020-10-13 16:30:00.0000000
0

A
Decontamination
Decontamination
2020-10-14 13:30:00.0000000
1

B
Other
Decontamination
2020-10-12 08:30:00.0000000
1

B
Decontamination
Decontamination
2020-10-12 14:30:00.0000000
1

B
Washer
Washer
2020-10-12 14:45:00.0000000
0

B
Decontamination
Decontamination
2020-10-12 15:15:00.0000000
0

B
Decontamination
Decontamination
2020-10-12 18:00:00.0000000
1

B
Washer
Washer
2020-10-13 18:15:00.0000000
0

B
Decontamination
Decontamination
2020-10-13 19:00:00.0000000
1


with 
	data as (
		select item, station_type, station_name, timestamp, 
			row_number() over(partition by item order by timestamp) rn
		from samplerfiddata sf
	),
	cte as (
		select d.*, timestamp first_timestamp 
		from data d 
		where rn = 1
		union all
		select d.*, 
			case when 'Decontamination' in (d.station_type, d.station_name) and d.timestamp > dateadd(hour, 2, c.first_timestamp)
				then d.timestamp
				else c.timestamp
			end
		from cte c
		inner join data d on d.item = c.item and d.rn = c.rn + 1
	)
select *
from cte
order by item, timestamp


item
station_type
station_name
timestamp
rn
first_timestamp


A
Decontamination
Decontamination
2020-10-10 06:30:00.0000000
1
2020-10-10 06:30:00.0000000

A
Decontamination
Decontamination
2020-10-11 14:30:00.0000000
2
2020-10-11 14:30:00.0000000

A
Washer
Washer
2020-10-11 14:45:00.0000000
3
2020-10-11 14:30:00.0000000

A
Decontamination
Decontamination
2020-10-11 15:15:00.0000000
4
2020-10-11 14:45:00.0000000

A
Other
Decontamination
2020-10-11 23:30:00.0000000
5
2020-10-11 23:30:00.0000000

A
Washer
Washer
2020-10-12 00:15:00.0000000
6
2020-10-11 23:30:00.0000000

A
Other
Decontamination
2020-10-12 00:45:00.0000000
7
2020-10-12 00:15:00.0000000

A
Other
Decontamination
2020-10-13 16:00:00.0000000
8
2020-10-13 16:00:00.0000000

A
Other
Decontamination
2020-10-13 16:30:00.0000000
9
2020-10-13 16:00:00.0000000

A
Decontamination
Decontamination
2020-10-14 13:30:00.0000000
10
2020-10-14 13:30:00.0000000

B
Other
Decontamination
2020-10-12 08:30:00.0000000
1
2020-10-12 08:30:00.0000000

B
Decontamination
Decontamination
2020-10-12 14:30:00.0000000
2
2020-10-12 14:30:00.0000000

B
Washer
Washer
2020-10-12 14:45:00.0000000
3
2020-10-12 14:30:00.0000000

B
Decontamination
Decontamination
2020-10-12 15:15:00.0000000
4
2020-10-12 14:45:00.0000000

B
Decontamination
Decontamination
2020-10-12 18:00:00.0000000
5
2020-10-12 18:00:00.0000000

B
Washer
Washer
2020-10-13 18:15:00.0000000
6
2020-10-12 18:00:00.0000000

B
Decontamination
Decontamination
2020-10-13 19:00:00.0000000
7
2020-10-13 19:00:00.0000000


with 
	data as (
		select item, station_type, station_name, timestamp, 
			row_number() over(partition by item order by timestamp) rn
		from samplerfiddata sf
	),
	cte as (
		select d.*, timestamp first_timestamp 
		from data d 
		where rn = 1
		union all
		select d.*, 
			case when 'Decontamination' in (d.station_type, d.station_name) and d.timestamp > dateadd(hour, 2, c.first_timestamp)
				then d.timestamp
				else c.timestamp
			end
		from cte c
		inner join data d on d.item = c.item and d.rn = c.rn + 1
	)
select item, count(*) cnt_cycles
from cte
where timestamp = first_timestamp
group by item


item
cnt_cycles


A
5

B
4

Item	Station_Type	Station_Name	Timestamp	Trying_to_Create_this_Flag_Column
A	Decontamination	Decontamination	2020-10-10 06:30:00.0000000	1
A	Decontamination	Decontamination	2020-10-11 14:30:00.0000000	1
A	Washer	Washer	2020-10-11 14:45:00.0000000	0
A	Decontamination	Decontamination	2020-10-11 15:15:00.0000000	0
A	Other	Decontamination	2020-10-11 23:30:00.0000000	1
A	Washer	Washer	2020-10-12 00:15:00.0000000	0
A	Other	Decontamination	2020-10-12 00:45:00.0000000	0
A	Other	Decontamination	2020-10-13 16:00:00.0000000	1
A	Other	Decontamination	2020-10-13 16:30:00.0000000	0
A	Decontamination	Decontamination	2020-10-14 13:30:00.0000000	1
B	Other	Decontamination	2020-10-12 08:30:00.0000000	1
B	Decontamination	Decontamination	2020-10-12 14:30:00.0000000	1
B	Washer	Washer	2020-10-12 14:45:00.0000000	0
B	Decontamination	Decontamination	2020-10-12 15:15:00.0000000	0
B	Decontamination	Decontamination	2020-10-12 18:00:00.0000000	1
B	Washer	Washer	2020-10-13 18:15:00.0000000	0
B	Decontamination	Decontamination	2020-10-13 19:00:00.0000000	1