CODEWITHSUNDEEP
snowflake-cloud-data-platform
user3369545
user3369545

Reputation: 411

Snowflake SQL Query to filter rows until the first occurence of a column value

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

Upvotes: 0

Views: 564

Answers (1)

Schnurres
Schnurres

Reputation: 177

Couldn't you do something like

INSERT INTO ##output
( event_time
, event_type
, user_id
, ctextid
, vbillid)
SELECT event_time
     , event_type
     , user_id
     , ctextid
     , vbillid
FROM ##input
WHERE event_time <
      (SELECT TOP(1) event_time FROM ##input WHERE event_type = 'Coding Complete Clicked' order by event_time ASC)
ORDER BY event_time ASC

Upvotes: 1

Related Questions