Does it has to be in one select? E.g. say, can you select all your operators first, then check, if their routes and dates match the interval?

Also please apply Fabio's suggestion to this select before anyone can proceed making any sense of it.

>
>I agree - it's taking far too long now. Below is how it was before I added the IFF( IFF( etc. ...
>
>As I said in the 1st post, it's checking for the approp. RteFam rec for eac ET rec's date.
>A RteFAm (route family) rec has dates of currency. Sometimes and ET rec can be before, during or after this date. If there's no current RteFam rec, for an Oerator/Route combo then # is put in the output; if there is no rec at all (current or not) to match trhen "?" us the result.
>
>I've just realised, The RteFam recs may have no start/stop dates, meaning they're "infinite". They may have an end date but no start date, or vice versa, in which case they're current any time up to the end date, or any time after the start date, respectively. Hmmm. I must smoke on um some more. :-)
>
>
>
>

>Wait WINDOW "ETM Data: Finding Journeys within the Time periods ..." NOWAIT NOCLEAR
>* For Now>	SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
>* For Now>		IIF( ET.Route == RF1.ERoute, RF1.ServType, "?") as ServeType, ;	
>* For Now>		IIF( ET.Route == ET.Mcl_Rte, "    ", ET.Mcl_Rte) as ContrNum, ET.Family, ;
>* For Now>		SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
>* For Now>		SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;
>* For Now>	  FROM ETMData ET ;
>* For Now>		JOIN Operator op ;
>* For Now>			On 	ET.Operator 	== op.Code ;
>* For Now>		LEFT JOIN RF1 ;
>* For Now>			On	ET.Operator 	== RF1.Operator ;
>* For Now>	        and (	ET.Route	== RF1.ERoute ;
>* For Now>	      	     or EMPTY( RF1.ERoute) ) ;
>* For Now>	  WHERE (	( ET.date BETWEEN ldStartCurr and ldEndCurr) ;
>* For Now>	  		 OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ) ;
>* For Now>	  AND not DELETED() ;
>* For Now>	  GROUP BY	ET.Mcl_Rte, ET.Operator, ET.Route, ET.Class ;
>* For Now>	  ORDER BY	ET.Operator, OpDesc, ET.Route, ET.Class ;
>* For Now>	  INTO CURSOR csrETMPassengers NOFILTER
>
>SELECT DISTINCT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
>	IIF( 	 ET.Route 		== RteFam.ERoute ;
>	     AND ET.Operator	== RteFam.Operator, ;
>		 IIF( ((   BETWEEN( ldStartCurr, RteFam.Start_date, RteFam.End_date) ;
>	  		    OR BETWEEN( ldEndCurr, RteFam.Start_date , RteFam.End_date) ;
>  			    OR ( RteFam.Start_date < ldStartCurr AND RteFam.End_date > ldEndCurr) ;
>  			    OR ( RteFam.Start_date => ldStartCurr AND RteFam.End_date <= ldEndCurr) ; 		  		    OR BETWEEN( ldStartPrev, RteFam.Start_date, RteFam.End_date) ;
>  			    OR BETWEEN( ldEndPrev, RteFam.Start_date, RteFam.End_date) ;
>  			    OR ( RteFam.Start_date < ldStartPrev AND RteFam.End_date > ldEndPrev) ;
>  			    OR ( RteFam.Start_date => ldStartPrev AND RteFam.End_date <= m.ldEndPrev))), ;
>  			  RteFam.ServType, "#"), ;
>  		 "?") as ServeType, ;	
>	IIF( ET.Route == ET.Mcl_Rte, "    ", ET.Mcl_Rte) as ContrNum, ET.Family, ;
>	SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
>	SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;
>  FROM ETMData ET ;
>	JOIN Operator op ;
>		On 	ET.Operator 	== op.Code ;
>	LEFT JOIN RteFam ;
>		On	ET.Operator 	== RF1.Operator ;
>        and (	ET.Route	== RF1.ERoute ;
>      	     or EMPTY( RF1.ERoute) ) ;
>  WHERE (	( ET.date BETWEEN ldStartCurr and ldEndCurr) ;
>  		 OR ( ET.date BETWEEN ldStartPrev and ldEndPrev) ) ;
>  AND not DELETED() ;
>  GROUP BY	ET.Mcl_Rte, ET.Operator, ET.Route, ET.Class ;
>  ORDER BY	ET.Operator, OpDesc, ET.Route, ET.Class ;
>  INTO CURSOR csrETMPassengers NOFILTER
>
>

>
>Essentially it's (should be) saying "If there is a matching operator/route combo, in the RteFam table, well if it's dates are current with the ET rec's date, use its SERVTYPE field - else put a '#' (to denote out-of-date). If there is no match, put a '?' to denote 'not known'"