>Here's an SQL SELECT that I'v cobbled together that gave me the results I was looking for:
>SELECT hst.order_date, bsn.store_name, hst.total, hst.earned ;
> FROM history AS hst, business AS bsn;
> WHERE bsn.vchr_code==hst.vchr_code .AND. ;
> BETWEEN(hst.order_date,{^2001-09-01},{^2002-08-31}) ;
> ORDER BY bsn.store_name, hst.order_date ;
> INTO TABLE ecrtemp.DBF
>Now, how do I get it to add a 'blank' row (a name but no date-total-earned)
>for records in the business table that do not have any matching data in history?
Does this work for you?
SET NULLDISPLAY TO ""
SELECT hst.order_date, bsn.store_name, hst.total, hst.earned ;
FROM history AS hst ;
RIGHT JOIN business AS bsn ON bsn.vchr_code==hst.vchr_code ;
.AND. BETWEEN(hst.order_date,{^2001-09-01},{^2002-08-31}) ;
ORDER BY bsn.store_name, hst.order_date ;
INTO TABLE ecrtemp.DBF