[SQL] Converting python logic to sql query

I need help with converting my python code to SQL:

req_id_mem = ""
req_workflow_mem = ""

collect_state_main = []
collect_state_temp = []

for req_id, req_datetime, req_workflow in zip(df["TICKET_ID"], df["DATETIMESTANDARD"], df["STATUS"]):
if req_id_mem == "" or req_id_mem != req_id:
req_id_mem = req_id
req_workflow_mem = ""
collect_state_temp = []

if req_workflow_mem == "" and req_workflow == "Pending Complete" and req_id_mem == req_id:
req_workflow_mem = req_workflow
collect_state_temp.append(req_id)
collect_state_temp.append(req_workflow)
collect_state_temp.append(req_datetime)

if req_workflow_mem == "Pending Complete" and req_workflow == "Inforce" and req_id_mem == req_id:
req_workflow_mem = req_workflow
collect_state_temp.append(req_workflow)
collect_state_temp.append(req_datetime)
collect_state_main.append(collect_state_temp)
collect_state_temp = []


DataFrame:

TICKET_ID DATETIMESTANDARD STATUS
79355138 9/3/2024 11:54:18 AM Open
79355138 9/3/2024 9:01:12 PM Open
79355138 9/6/2024 4:52:10 PM Closed
79355138 9/6/2024 4:52:12 PM Open
79355138 9/10/2024 4:01:24 PM Closed
79446344 8/27/2024 1:32:54 PM Open
79446344 9/11/2024 9:40:17 AM Closed
79446344 9/11/2024 9:40:24 AM Closed
79446344 9/11/2024 9:42:14 AM Open

Result:

1. It will Identify the first Open State of a TICKET_ID and look for the closest Closed Status
2. It will reiterate for each case to look for an Open and Closed pair (first open and first close will only be considered)

My problem is I'm stuck since the pairings can happen more than twice. I tried Rank in sql but it only return the first instance of pairing but not the other pairs

Continue reading...