... GROUP BY a_sup, a_vin, ; ...>I am having difficulty in using my sql statement. What I have is a table that has supervisor, vehicle number, mileage, assign date. I have a form where the user puts in starting assign date and ending assign date. Both dates a looking at a_startdate. What I am trying to do is group supervisor by their a_vin which would look the following
>sup1 veh1 100 200 100 someavg someunit >sup1 veh2 100 300 200 someavg someunit >sup2 veh3 100 200 100 someavg someunit >sup2 veh4 100 200 100 someavg someunit >sup2 veh5 100 200 100 someavg someunit >..... >sup9 veh20 20 40 20 someavg someunit > >>
days = (.todate.value-.fromdate.value)*30 > >SELECT a_id, a_sup, a_vin, MIN(a_startmile) as begin, MAX(a_startmile) as end, ; > MIN(a_startmile)-MAX(a_startmile) as totalmile, ; > (MIN(a_startmile)-MAX(a_startmile))/days as average, ; > a_unit as unit FROM veh_assign_tbl ; >GROUP BY a_sup WHERE BETWEEN(a_startdate, .fromdate.value, .todate.value) ; >INTO CURSOR test > brows>