Try

SELECT ;
    Clients.ClientName, ;
    Sum(IIF(BETWEEN(Reservations.date, ReportFrom, ReportTo), 1, 0) as CurRes,;
    Sum(IIF(BETWEEN(Reservations.date, ReportFrom, ReportTo), Reservations.Amount, 0) as CurAmount, ;
    Sum(IIF(BETWEEN(Reservations.date, OldFrom, OldTo), 1, 0) as OldRes,;
    Sum(IIF(BETWEEN(Reservations.date, OldFrom, OldTo), Reservations.Amount, 0) as OldAmount, ;
  FROM Reservations JOIN Clients ;
        ON Reservations.ClientCode=Clients.Code  ;
  WHERE between( Reservations.date, ReportFrom, ReportTo ) ;
         OR between( Reservations.date, OldFrom, OldTo ) ;
  GROUP BY Clients.Code ;
INTO CURSOR crsResult	 

>Hi All
>I have the following two tables:
>Reservations ( ResNo C(6),ClientCode C(5),Amount N(9,2), Date D )
>Clients ( Code C(5), ClientName C(30) )
>I want to retrieve in one SELECT the number of reservations and the total amount grouped by client, for two time periods ( current and last year ). The current time period will be BETWEEN ( date, ReportFrom, ReportTo ), and the old time period will be BETWEEN( date, OldFrom, OldTo ).
>I am trying to run the following select command:

>SELECT Clients.ClientName, Count(Reservations.ResNo) as CurRes,;
> sum(Reservations.Amount) as CurAmount,0000 as OldRes,00000.00 as Oldamount ;
> from Reservations, Clients ;
> where Reservations.ClientCode=Clients.Code and ;
>   between( Reservations.date, ReportFrom, ReportTo ) ;
> group by 1 ;
>union ( ;
>SELECT Clients.ClientName, 0000 as CurRes,00000.00 as CurAmount,;
> count(Reservations.ResNo) as OldRes,sum(reservations.amount) as Oldamount ;
> from Reservations, Clients ;
> where Reservations.ClientCode=Clients.Code and ;
>   between( Reservations.date, OldFrom, OldTo ) ;
> group by 1 )

>I have two problems: first, when running this, some clients come once, and some twice. Also, i tried to put an order by clause, but wherever i put it ( i.e in the end of the fist select ) i get a "Missig ) parentheses" error.
>
>TIA
>Jaime