gistfile1.sql

WITH 
schedule1 AS (
SELECT 
	q2.object_id,
	q2.trip_id,
	q2.rule_id,
	rule_type,
	rule_name,
	greatest(q2.tff, q2.tf) as tff,
	min(e1.datetime) as tf,
	-- q2.tf,
	tfp,
	t1,
	t2,
	tpn
FROM  (SELECT 
		q1.object_id,
		q1.trip_id,
		q1.rule_id,
		rule_type,
		rule_name,
		min(e1.datetime) as tf,
		coalesce(lag( min(e1.datetime) ) over (PARTITION BY q1.object_id, q1.trip_id, date(q1.t1) ORDER BY q1.t1), t1) as tff,
		tfp,
		t1,
		t2,
		tpn
		
	FROM   (SELECT t.object_id, t.id as trip_id, r.id as rule_id, 
		       (case when rt.name = 'in_geo_polygon' then 'IN' else (case when rt.name = 'out_of_geo_polygon' then 'OUT' else rt.name end) end) as rule_type,
		       r.name as rule_name,
		       r.start_time as rule_start_time,
		       null::timestamp as tfp,
		       null::timestamp as tf,
		       r.start_time - interval '30 minutes' as t1,
		       r.start_time + interval '30 minutes' as t2,
		       coalesce(lead(r.start_time) over w - interval '30 minutes', r.stop_time) as tpn
		       
		FROM trip_rule r JOIN trip t ON r.trip_id = t.id
				 JOIN trip_rule_type rt on rt.id = r.rule_type_id
		WHERE t.active
		AND t.object_id in (5,19,27)
		AND date(r.start_time) > '2011-01-31'
		AND date(r.start_time) <= date(now())
		AND rt.name in ('in_geo_polygon', 'out_of_geo_polygon')
		WINDOW w AS (PARTITION BY t.object_id, t.id, date(r.start_time) ORDER BY r.start_time)
		ORDER BY t.object_id, t.id, r.start_time
		) AS q1
		JOIN trip_rule rr on rr.id = q1.rule_id
		LEFT JOIN object_event e1 ON e1.object_id = q1.object_id 
		     AND date(e1.datetime) = date(q1.rule_start_time) AND e1.fix = 'gps'
		     AND e1.datetime BETWEEN q1.t1 AND q1.t2
		     AND ((rule_type = 'IN' AND st_within(e1.coordinate, rr.polygon))
		       OR (rule_type = 'OUT' AND NOT st_within(e1.coordinate, rr.polygon)))
		
	GROUP BY q1.object_id, q1.trip_id, q1.rule_id,
		 q1.rule_type,
		 q1.rule_name,	
		 q1.tfp,
		 q1.tf,
		 q1.t1,
		 q1.t2,
		 q1.tpn
	ORDER BY date(q1.t1), q1.object_id, q1.trip_id, q1.t1
	) AS q2
	JOIN trip_rule rr on rr.id = q2.rule_id
 	LEFT JOIN object_event e1 ON e1.object_id = q2.object_id 
 	     AND date(e1.datetime) = date(q2.t1) AND e1.fix = 'gps'
 	     AND e1.datetime BETWEEN greatest(q2.tff, q2.t1)  AND q2.t2
 	     AND ((rule_type = 'IN' AND st_within(e1.coordinate, rr.polygon))
 	       OR (rule_type = 'OUT' AND NOT st_within(e1.coordinate, rr.polygon)))
 	GROUP BY q2.object_id, q2.trip_id, q2.rule_id,
 		 q2.rule_type,
 		 q2.rule_name,
 		 q2.tff,
 		 q2.tfp,
 		 q2.tf,
 		 q2.t1,
 		 q2.t2,
 		 q2.tpn
 	WINDOW w1 AS (PARTITION BY q2.object_id, q2.trip_id, date(q2.t1) ORDER BY q2.t1)
        ORDER BY date(q2.t1), q2.object_id, q2.trip_id, q2.t1		 
	)

SELECT * FROM schedule1;