The SQL below is intended to get details of routes that run in certain date ranges, the purpose is to compare passenger usage in one (current) period with that in a similar (previous) period (USUALLY A YEAR APART).

Each rec in the Details table (NotETM) has a date range for a period across which passenger nos. were recorded (by ticket m/c). So long as data are recorded SOMEWHERE within the report periods the recs are included as in the below diag (For those recs whose range overlaps either or both end(S) of the report periods, later processes apportion the number of passengers that fall within the report periods).

        ldStartPrev ................... ldEndPrev       ldStartCurr ................... ldEndCurr
             |                              |                |                              |
>------------------------->                           >------------------------->
               <-------------------->                             <-------------------->
                       <---------------------------<                       <---------------------------<
     <------------------------------------------>         <------------------------------------------>

SELECT NET.Operator, NET.Route, NET.Start_Date, NET.End_Date, NET.RorF, NET.Passengers, ;
	IIF( NET.Route == NET.Mcl_Rte, "    ", NET.Mcl_Rte) as ContrNum, NET.Family, NET.ClassType, ;
	0000000 AS CurrPass, 0000000 AS PrevPass, op.Desc as OpDesc, ;
	Thisform.lmGetServeType( NET.Operator, NET.Route, NET.Start_date) as ServeType ;
  FROM NotETM NET ;
	JOIN Operator op ;
		On 	NET.Operator 	== op.Code ;
  WHERE (     ( m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ;
  	  OR ( m.ldEndCurr BETWEEN NET.Start_date   AND NET.End_date) ;
  	  OR ( NET.Start_date < m.ldStartCurr       AND NET.End_date > m.ldEndCurr) ;
  	  OR ( NET.Start_date >= m.ldStartCurr      AND NET.End_date <= m.ldEndCurr) ; 		  
  	  OR ( m.ldStartPrev BETWEEN NET.Start_date AND NET.End_date) ;
  	  OR ( m.ldEndPrev   BETWEEN NET.Start_date AND NET.End_date) ;
  	  OR ( NET.Start_date < m.ldStartPrev       AND NET.End_date > m.ldEndPrev) ; 
  	  OR ( NET.Start_date >= m.ldStartPrev      AND NET.End_date <= m.ldEndPrev)) ;
  AND not DELETED("NotETM") ;
  ORDER BY	NET.Operator, OpDesc, NET.Route ;
  INTO CURSOR csrNotETMPassengers NOFILTER READWRITE 

This has been working fine up to now. BUT the report has just been run against two CONTIGUOUS date ranges. It seems that certain records from the PREV. period are being ommitted, probably because part of their range overlaps the CURR period. The totals for CURR period recs are fine.

I tried splitting each report period into separate bracketed sections, as below, but to no avail:

  WHERE ((     (  m.ldStartCurr BETWEEN NET.Start_date AND NET.End_date) ;
  	   OR (	NET.Start_date < m.ldStartCurr       AND NET.End_date > m.ldEndCurr) ;
  	   OR (	NET.Start_date >= m.ldStartCurr      AND NET.End_date <= m.ldEndCurr) ; 		  
  	   OR (	m.ldEndCurr   BETWEEN NET.Start_date AND NET.End_date)) ;
  	  	OR ;
         (     (	m.ldStartPrev BETWEEN NET.Start_date AND NET.End_date) ;
  	   OR (	NET.Start_date < m.ldStartPrev       AND NET.End_date > m.ldEndPrev) ; 
  	   OR (	NET.Start_date >= m.ldStartPrev      AND NET.End_date <= m.ldEndPrev) ;
  	   OR (	m.ldEndPrev   BETWEEN NET.Start_date AND NET.End_date)));
  AND not DELETED("NotETM") ;

Can anyone figure this one out?

'ppreciate it

Terry