Hi again Sergey

Here was the original code (which you kindly helped with last summer, BTW), which I have now reinstated.

SELECT ET.Operator, ET.Route, ET.Class, ET.Date, ET.RorF, op.Desc as OpDesc, ;
	Thisform.lmGetServeType( ET.Operator, ET.Route, ET.Date) 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 ;
  WHERE ET.Operator 		== lnOpCode ;
  AND	(   ET.date BETWEEN ldStartCurr and ldEndCurr ;
  	 OR ET.date BETWEEN ldStartPrev and ldEndPrev) ;
  AND not DELETED() ;
  ORDER BY	ET.Operator, OpDesc, ET.Route, ET.Class ;
  GROUP BY	ET.Mcl_Rte, OpDesc, ET.Operator, ET.Route, ET.Class ;
  INTO CURSOR csrETMPassengers NOFILTER READWRITE 

As I said in another message, I was barking up the wrong tree. The start and end dates of the 2 report periods (prev and curr) don't change. The problem with this report is that the value ET.Number is each to be multiplied by a "multiplier", depending on the ET rec's "Class", got from an Operator-Class table

Now these Classes go by operator and date. Sometimes an operator can have the same Class no., but it may have expired, so there'll be a new one. Each of these recs has a start and end date - some being open.

eg

Operator  Class  StartDate  EndDate   Multiplier
13        0037   --------   31/03/06           2
13        0037   01/04/06   --------           1

FYI the class is to do with bus ticket sales., from an electronic ticket machine (ETM). If a mult. of 2 then it was a return ticket. In the last year all bus journeys for pensioners have been made free by the govt. so this prog is comparing these concessionary fares/sales between similar periods, 2 different years.

Problem is with open-ended dates like above, in the code that works out the no. tickets * mult (Pass 2 - somewhere else on the form), sales for, say op=13, class=0037 are getting totted twice, once for each class, so we get this combo duplicated, one being twice the previous.

I'm trying to trace where this happens, but I'm a bit rusty on this form and so was looking in the wrong place. As it is I STILL haven't found where the dupes are happening.

Cheers


>Hi Terry,
>
>In your query you have 2 periods: ldStartCurr - ldEndCurr and ldStartPrev - ldEndPrev. According to you query itself didn't change but definiton of those periods did (sometimes).
>You can either create and use new variables for those periods if you have to preserve their values or assign new values to the same variables. Something like
>

>IF EMPTY(ldEndPrev)
>	ldEndPrev = ldStartCurr
>ENDIF
>

>
>
>>Sorry, I missed that part of your original message when I got engrossed in answering the first part.
>>
>>How redefine start and end of the periods?
>>
>>>If you follow my suggestion, you wouldn't have embedded IIF() in the first place.
>>>
>>>>Hi Sergey. Thanks for the attention.
>>>>
>>>>You see the old snippet below, commented out?
>>>>So, if ET.Date was between the curr period start and end dates it was summed.
>>>>Similarly, if it fell between the prev period start and end dates it was summed.
>>>>
>>>>However, there might not be start curr period date, or end prev period date so, respectively, the ET.Date would need to be between the end prev period and end curr period, or start prev period and start curr period.
>>>>
>>>>Whether or not my logic is flawed here (:-), I amn experimenting to get mself out of an anomolie that's not worth (or really possible easily) to explain here, and I mainly want to know where the niggling error is. That's why I've elaborately laid out the code, to saee what args go with what funcs more easily.
>>>>
>>>>Cheers
>>>>
>>>>>Hi Terry,
>>>>>
>>>>>Your SQL doesn't match what you are saying you're trying to do. Can you clarify your intentions?
>>>>>Anyway, you should redefine begining and end of each period instead of complication query itself.