Rob Willett wrote:
> I’d like to produce an output table based on the above data set that looks a
> bit like this.
>
> Disruption_id | Start Time | End Time
> 1 | 1 | 4
> 2 | 1 | 5
> 3 | 1 | 2
> 4 | 2 | 4
Use correlated subqueries:
SELECT Disruption_id,
(SELECT MIN(Time_Event)
FROM Table_1 AS T2
WHERE T2.Disruption_id = T1.Disruption_id
) AS Start_Time,
(SELECT MIN(Time_Event)
FROM Table_1 AS T2
WHERE T2.Disruption_id = T1.Disruption_id
AND T2.State = 'Closed'
) AS End_Time
FROM (SELECT DISTINCT Disruption_id
FROM Table_1) AS T1
> 2. Any events that are still Active, e.g. Disruption_Id=5 in the above
> example would not be shown.
...
WHERE End_Time IS NOT NULL
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
