Derek,there is alot of hitting the tables for these easy calculations. Why not hit it one time and get all the totals like the first select statement or get the calculations then total them then combine with header files like the second select statement. I hope this helps. Sometimes a different approach my make all the difference. Good Luck...


*-This hits all the tables at once
*-It may be slower than the one below but definately faster than the code
*-you showed me.
*-
select ;
&lcSortA as xSortBy, ;
&lcFields1 ;
sum(iif(cType = "Labor",nRegHrs + nOverHrs + nDoubleHrs,0)) as nTotalHours ,;
sum(iif(cType="Labor",nTotalCost, 0)) as nTotalLabor, ;
*
* other 'cType' totals go hear
*
*
from Jstran, Jsjord, Arcust ;
where Jsjord.cCustNo = Arcust.cCustNo and ;
JsJord.cjob = JsTran.cjob ;
&lcFilter1 &lcOpenAsOf;
group by cjob;
union all
select ;
&lcSortB as xSortBy, ;
&lcFields2, ;
sum(iif(cType = "Labor",nRegHrs + nOverHrs + nDoubleHrs,0)) as nTotalHours ,;
sum(iif(cType="Labor",nTotalCost, 0)) as nTotalLabor, ;
*
* other 'cType' totals go hear
*
*
from Jsjordh, Arcust, jstranh ;
where Jsjordh.cCustNo = Arcust.cCustNo and ;
jstranh.cjob = jsjordh.cjob ;
&lcFilter1 &lcOpenAsOf;
group by cjob;
into cursor CurReport ;
order by &lcOrder






*-This hits the calculations tables then
*-you will combine them with the arcust and jord tables
*-This select statement will probably be faster because
*-the calculation are done first.
select cjob,;
sum(iif(cType = "Labor",nRegHrs + nOverHrs + nDoubleHrs,0)) as nTotalHours ,;
sum(iif(cType="Labor",nTotalCost, 0)) as nTotalLabor, ;
*
* other 'cType' totals go hear
*
*
from Jstran
group by cjob;
union all
select cjob,;
sum(iif(cType = "Labor",nRegHrs + nOverHrs + nDoubleHrs,0)) as nTotalHours ,;
sum(iif(cType="Labor",nTotalCost, 0)) as nTotalLabor, ;
*
* other 'cType' totals go hear
*
*
from jstranh ;
group by cjob;
into cursor sys(2015)
lcAlias = Alias()
select ;
cjob
sum(ntotalhours) as ntotalhours ,;
sum(ntotallabor) as ntotallabor ,;
*
* other totals (inventory, subcontract...)
from (lcAlias)
group by cjob
into cursor sys(2015)
use in (lcAlias)
lcAlias = ALIAS()

**then combine with Jsjordh, Arcust, jsjord tables
**i didn't have time to work this part out but I think
**you can see what i was trying to accomplish.