What is the average throughput time when activity ’End trip’ occurs more than 50 days…
- User
What is the average throughput time when activity ’End trip’ occurs more than 50 days after the start of the case?
Can you provide me a SQLite SQL query. You should use the unixepoch function of SQLite to get the timestamp from the date.
The data is stored in a table where each row is an event having the provided attributes (so there are no separate table containing the variant). The table is called ”events_log”.
Please consider the following information: the case identifier is called ”case_id”, the activity is stored inside the attribute ”activity”, the timestamp is stored inside the attribute ”timestamp”, the resource is stored inside the attribute ”resource”. There is not a variant column but that can be obtained as concatenation of the activities of a case. There is not a duration column but that can be obtained as difference between the timestamp of the first and the last event.
- Assistant
To compute the average throughput time for cases in which the activity 'End trip' occurs more than 50 days (50 days * 24 hours/day * 60 minutes/hour * 60 seconds/minute = 4320000 seconds) after the start of the case, you can use the following SQL query in SQLite:
WITH case_start_end_times AS ( -- Getting the timestamp of the first and the last event for each case. SELECT case_id, MIN(strftime('%s', timestamp)) AS start_time, MAX(strftime('%s', timestamp)) AS end_time FROM events_log GROUP BY case_id ), end_trip_times AS ( -- Getting the timestamp of 'End trip' events for each case. SELECT case_id, strftime('%s', timestamp) AS end_trip_time FROM events_log WHERE activity = 'End trip' ), filtered_cases AS ( -- Finding cases where 'End trip' happens more than 50 days after case start. SELECT se.case_id, se.start_time, se.end_time, et.end_trip_time FROM case_start_end_times se JOIN end_trip_times et ON se.case_id = et.case_id WHERE (et.end_trip_time - se.start_time) > 4320000 ) -- Calculating the average throughput time for the filtered cases. SELECT AVG(end_time - start_time) AS avg_throughput_time FROM filtered_cases;Here's what the query does:
-
In the first CTE (
WITHclause),case_start_end_times, it selects the minimum timestamp (case start time) and maximum timestamp (case end time) for each case. -
In the second CTE,
end_trip_times, it selects the timestamp of the 'End trip' activity for each case. -
In the third CTE,
filtered_cases, it joins the first two CTEs and filters for those cases where the 'End trip' occurs more than 4320000 seconds (which is equivalent to 50 days) after the start of the case. -
Finally, it calculates the average throughput time by subtracting the start time from the end time for the filtered cases.
Please verify the formatting of your timestamps before using the query, as SQLite's
strftime('%s', timestamp)function expects the timestamp to be in a recognizable SQLite datetime format. If your timestamps are already in Unix epoch format, you may need to adjust the usage ofstrftime. -