>Got this complex SQL. The field ET.Number is to be summed if it falls between the prev. start and end dates, or between the curr. start and end dates.
>
>That's how it stood and worked OK. BUT, sometimes the date ranges can be open-ended, eg there's a prev. period start date but "open-ended" end date. So had to extend the SQL to say, like, "if ther's no end to prev period make sure the date is before the start of the curr. period)
>
>Trouble is:
>"too many arguments" error. Can't figure where it's going wrong. Need another pair of eyes looking over my shoulder. Can anyone spot the error?
>
>'ppreciate it.
>
>
>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, ;
> IIF( not EMPTY( ldStartCurr), ;
> ldStartCurr, ;
> ldEndPrev), ;
> ldEndCurr), ;
> ET.Number, ;
> 0000000) ) AS CurrPass, ;
> SUM( IIF( BETWEEN( ET.date, ;
> ldStartPrev, ;
> IIF( not EMPTY( ldEndPrev), ;
> ldEndPrev, ;
> ldStartCurr), ;
> 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
>
>
>For ref. the last 2 SUM()s were as follows:
>
>* 30/08/07 SUM( IIF( BETWEEN( ET.date, ldStartCurr, ldEndCurr), ET.Number, 0000000)) AS CurrPass, ;
>* 30/08/07 SUM( IIF( BETWEEN( ET.date, ldStartPrev, ldEndPrev), ET.Number, 0000000)) AS PrevPass ;
Take a look in your last SUM(). You have too many arguments in the between function:
SUM( IIF( BETWEEN(ET.date, ;
ldStartPrev, ;
IIF( not EMPTY( ldEndPrev), ;
ldEndPrev, ;
ldStartCurr), ;
ET.Number, ;
0000000) ) AS PrevPass ;So that should be:
SUM( IIF( BETWEEN(ET.date, ;
ldStartPrev, ;
IIF( not EMPTY( ldEndPrev), ;
ldEndPrev, ;
ldStartCurr)), ;
ET.Number, ;
0000000) ) AS PrevPass ;
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.
