Filtering rows to till the first occurence of a column value

Transact-SQL
1

Hi All ,

Hope you are doing well...I am trying to output the rows until the first occurrence of the value "Coding Complete Clicked" in the input table (column -> event_type) ..I need to exclude the rows post the first occurrence of the value "Coding Complete Clicked" ordered by the column event_time in ascending order.. Please find the DDL of the input and output tables..Can you please help me here..

Input

Create table ##input
(event_time datetime2,
event_time varchar(1000),
user_id varchar(30),
ctextid int,
vbillid int)

insert into ##input values
('1/11/2022 1:21:02 PM','Start Work Clicked','am21','10021','21'),
('1/11/2022 1:46:02 PM','Coding Complete Clicked','am21','10021','21'),
('1/12/2022 1:46:02 PM','Start Work Clicked','am31','10021','21'),
('1/12/2022 1:51:02 PM','Home Button Clicked','am31','10021','21'),
('1/13/2022 1:21:02 PM','VisitBill Button Clicked','am12','8921','31'),
('1/13/2022 1:31:02 PM','Home Button Clicked','am12','8921','31'),
('1/13/2022 1:51:02 PM','VisitBill Button Clicked','am13','8921','31'),
('1/13/2022 2:51:02 PM','Coding Complete Clicked','am13','8921','31'),
('1/13/2022 2:57:02 PM',' Icon Clicked','am14','8921','31'),
('1/13/2022 3:57:02 PM','VisitBill Button Clicked','am14','8921','31'),
('1/13/2022 4:10:02 PM','Coding Complete Clicked','am14','8921','31'),
('1/15/2022 4:10:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 4:18:02 PM','Coding Complete Clicked','am78','9213','34'),
('1/15/2022 4:38:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 4:50:02 PM','Coding Complete Clicked','am78','9213','34'),
('1/15/2022 4:57:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 5:20:02 PM','Coding Complete Clicked','am78','9213','34')

output

create table ##output
(event_time datetime2,
event_time varchar(1000),
user_id varchar(30),
ctextid int,
vbillid int)

insert into ##output values
('1/11/2022 1:21:02 PM','Start Work Clicked','am21','10021','21'),
('1/11/2022 1:46:02 PM','Coding Complete Clicked','am21','10021','21'),
('1/13/2022 1:21:02 PM','VisitBill Button Clicked','am12','8921','31'),
('1/13/2022 1:31:02 PM','Home Button Clicked','am12','8921','31'),
('1/13/2022 1:51:02 PM','VisitBill Button Clicked','am13','8921','31'),
('1/13/2022 2:51:02 PM','Coding Complete Clicked','am13','8921','31'),
('1/15/2022 4:10:02 PM','VisitBill Button Clicked','am78','9213','34'),
('1/15/2022 4:18:02 PM','Coding Complete Clicked','am78','9213','34')

2 

row number for all rows 

case when event_type = 'Coding Complete Clicked' then 1 else 0 end  as ok 

min(row number )   group by  ok 

select * from table where row number less than or equal to min row number
3

other extreme short cuts with fancy logic ARE THERE :stuck_out_tongue_winking_eye: :stuck_out_tongue_winking_eye:

4

I'm pretty sure this is correct to give you the results you want. Can't tell for sure because your output doesn't match the sample data provided.


;WITH cte_user_id_with_first_event_end_time AS (
    SELECT user_id, MIN(event_time) AS ending_event_time
    FROM ##input
    WHERE event_type = 'Coding Complete Clicked'
    GROUP BY user_id
)
SELECT i.*
FROM ##input i
INNER JOIN cte_user_id_with_first_event_end_time c ON i.user_id = c.user_id AND 
    i.event_time <= c.ending_event_time
ORDER BY c.user_id, c.ending_event_time
5

Thank you so much @ScottPletcher !..This works ...Really appreciate your help!