Carl write a pure TSQL commands, He (I think) supposed you use SQL Server and DateTime fields.

>Carl
>
>Thanks for the input. Can't say I recognise the language you wryte the snippet in but it doesn't work as foxpro. ?
>The dates are not DateTime vars and the the Between function used in your code is not the Between() fn. of vfp, and will not work outside an SQL expression.
>
>So all in all I'm a bit confused as to what you're demonstrating. :-|
>
>Cheers
>
>Terry
>
>>>Hi Searchers
>>>
>>>The below SQL is certainly not getting me the results I expect and, worse still gives me 1 spurious result.
>>>
>>>I've already done a search to multiply the number of journies done on eligible bus services, by a certain factor, to give a "trips" total for each bus operator, route no + (ticket) class, et al, within 2 date ranges.
>>>
>>>Now I wish to list all the journies that didn't get included in the prev. report because although the (bus) operator had a (ticket) CLASS, the class didn't have an associated MULTIPLIER in the OpClass Table.
>>>

>>>OpClass Table is as follows:
>>>
>>>Operator#, Class#, Multiplier, ...
>>>
>>>And Operator:
>>>
>>>Operator#, Class#, ...
>>>

>>>
>>>The ETMDATA table holds details of all Electronic Ticket Machine (ETM) transactions over a period.
>>>The reason why OpClass needs the 2 keys is that a class is not unique to an operator, and certain class nos mean different things to different operators, but are issued by the ETM manufacturer.
>>>
>>>Have I got the NOT EXISTS logic wrong or what is it?
>>>
>>>'ppreciate it.
>>>
>>>Terry
>>>
>>>

>>>Select	DISTINCT ET.Operator as MLOperator, ET.Class as MLClass, ;
>>>		op.Desc as MLOpDesc, rf.ServType as MLServType, ;
>>>		SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS MLCurrPass, ;
>>>		SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS MLPrevPass ;
>>>  FROM ETMData ET ;
>>>	JOIN Operator op ;
>>>		On ET.Operator 	== op.Code ;
>>>	JOIN RteFam rf ;
>>>		On ET.Operator 	== rf.Operator ;
>>>		and ET.Route    	== rf.ERoute ;
>>>  WHERE  ET.date BETWEEN ldStartCurr and ldEndCurr ;
>>>  OR 	ET.date BETWEEN ldStartPrev and ldEndPrev ;
>>>  AND 	NOT EXISTS ;
>>>  	( Select * from OpClass ;
>>>  		WHERE OpClass.Operator == ET.Operator ;
>>> 		AND   OpClass.Class    == ET.Class ) ;
>>>  ORDER BY	ET.Operator, ET.Class ;
>>>  INTO Cursor csrMultless
>>>
>>>

>>
>>Terry,
>>
>>I have sure of issue but I do know that you should probably rethink using BETWEEN when doing SQL Datetime queries comparison
>>
>>Declare @ETdate datetime
>>declare @ldStartCurr datetime
>>declare @lEndPrev datetime
>>
>>set @ETdate = '07/20/2006 10:00:00'
>>set @ldStartCurr='07/20/2006 08:00:00'
>>set @lEndPrev = '07/20/2006 9:55:00'
>>
>>if (@ETdate between @ldStartCurr and @lEndPrev)
>>begin
>> print 'yes'
>>end
>>else
>> print 'no'
>>
>>ET.Date would not be considered between ldStartCurr and lEndPrev because is may fall in the date range but it does not fall in range when time is evaluated.
>>
>>This is just a guess...
>>
>>Carl