--Query 05: "Show map with minimum delay yesterday used to color the line 
-- segments"
-- For each src_dest pair, show minimum delay between them in a given day and
-- show the long, lat for each src, dest.

--MinDayDelayNodePairID: Generate a table of nodepairs with the min delay for a
-- given day

WITH \
-- Min Delay by Nodepair ID for a given day
minDayDelayNodePairID(NodePairID, MinDelay) as \
( \
SELECT	t.NodePairID as NodePairID, \
	min(t.Delay) as Delay \
From	Surveyor.Times t \
WHERE	t.Delay != -1 AND \
	t.start BETWEEN (954633600) AND (954633600 + 86400 - 1) \
GROUP BY	t.NodePairID \ 
) \
-- Join tmp Table with Nodes to provide Long, Lat information
SELECT	tmp.NodePairID as "PathSpec", \
	src.Lat as "Src Lat", src.Long as "Src Long", \
	dest.Lat as "Dest Lat", dest.Long as "Dest Long", \
	tmp.MinDelay as "Min Delay" \
FROM	Surveyor.NodePairs np, Surveyor.Nodes src, Surveyor.Nodes dest, \
	minDayDelayNOdePairID tmp \
WHERE	tmp.NodePairID = np.Id AND \
	np.SrcNodeId = src.NodeId AND \
	np.DestNodeId = dest.NodeId \
ORDER BY	tmp.NodePairID