CR10 on a Sql Database. Three Tables, Order,Leg, and Paydetails. Each order can have multiple legs that can have multiple paydetails. Legs are associated with Units<Tractor>, Paydetails with Driver. Order(numberID) is linked via inner join to Legs(Numberid), Legs linked to paydetails via left outer.
I've grouped by OrderNumberID, then sorted by Legs. I have to come up with the revenue for the Tractors and for the drivers. For the tractors, if the order line haul is 1000 for 500 miles, and the tractor ran all 500 miles, the tractor receives 100% of the Revenue, else they receive a percentage for the miles they ran. <total miles per order comes from adding up the legs of the order> I have a running total for calculating the legs for each tractor in each order <Sum of LGH_miles, evaluate for Lgh_Number, reset on ord_hdrnumber>
A driver can have multiple pay details per leg, per order. He could be paid a flat rate, as well as additional charges.
Ihave a running total set up for Drivers <Sum of Pyd_Amt, evaluate on pyd_number,reset on change of lgh_number>
This is all working so far... but now I need to group on the Tractors and Drivers to show their total Revenue and Pay. This is where I am getting lost. Should what I have so far be a subreport for a main report that does the final summations, or is their a way to continue using running totals? Below is what I have so far.
Julie Cox
CR 9 and 10
CE 10
Sql Database
I've grouped by OrderNumberID, then sorted by Legs. I have to come up with the revenue for the Tractors and for the drivers. For the tractors, if the order line haul is 1000 for 500 miles, and the tractor ran all 500 miles, the tractor receives 100% of the Revenue, else they receive a percentage for the miles they ran. <total miles per order comes from adding up the legs of the order> I have a running total for calculating the legs for each tractor in each order <Sum of LGH_miles, evaluate for Lgh_Number, reset on ord_hdrnumber>
A driver can have multiple pay details per leg, per order. He could be paid a flat rate, as well as additional charges.
Ihave a running total set up for Drivers <Sum of Pyd_Amt, evaluate on pyd_number,reset on change of lgh_number>
This is all working so far... but now I need to group on the Tractors and Drivers to show their total Revenue and Pay. This is where I am getting lost. Should what I have so far be a subreport for a main report that does the final summations, or is their a way to continue using running totals? Below is what I have so far.
Code:
ord_hdrnu lgh_linelgh_num pyd_numlgh_mi RTOrderM pyd_amou PydDriv lgh_tra lgh_dri
719927 $1,012. 179251 323342 562 562.00 $112.40 $254.80 252 OLIVTI
719928 $1,012. 179252 324047 562 562.00 $223.10 $223.10 208 STALDA
719928 $1,012. 179252 325881 562 562.00 $50.00 $273.10 208 STALDA
719929 $1,012. 179253 327259 320 320.00 $112.40 $112.40 252 OLIVTI
719929 $1,012. 179253 327260 320 320.00 $15.60 $128.00 252 OLIVTI
719929 $1,012. 180458 324856 422 742.00 $30.00 $30.00 246 HEMGLU
719929 $1,012. 180458 324857 422 742.00 $108.36 $138.36 246 HEMGLU
719929 $1,012. 180458 326588 422 742.00 $25.00 $163.36 246 HEMGLU
719930 $1,012. 179254 325960 281 281.00 $111.55 $111.55 208 STALDA
719930 $1,012. 179254 325961 281 281.00 $25.00 $136.55 208 STALDA
719930 $1,012. 180537 325958 440 721.00 $25.00 $25.00 253 HUBBRI
719930 $1,012. 180537 325959 440 721.00 $111.55 $136.55 253 HUBBRI
Julie Cox
CR 9 and 10
CE 10
Sql Database