OK, found HUGE stumbling block. The code below returns nothing if there is no rollover occuring in that day whereas I need it to return -32000 with that days date so it can be joined to the first two subqueries
SELECT (COUNT(*)-1)*32000 as [Rollover_Count]
,dateadd(DAY,0, datediff(day,0, t1.t_stamp)) AS [Date]
FROM AP7_E_Open_Flap_Test t1
FULL OUTER JOIN AP7_E_Open_Flap_Test t2
ON t1.AP7_E_Open_Flap_Test_ndx = t2.AP7_E_Open_Flap_Test_ndx -1
WHERE t2.open_front < t1.open_front AND t2.open_front IS NOT NULL
AND t1.t_stamp > '2013-06-06 00:00:00' and t1.t_stamp < '2013-06-06 11:59:00' -- Also need report dates here
group by dateadd(DAY,0, datediff(day,0, t1.t_stamp))
Bah - come all this way and now I'm at a dead end 🙁