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')
;
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 |