1
Large DB, It does a Primary sort- passing 350000 recs sorted. it thenn does a sum on one table, using a SELECT SUM, Then it reads a Secod time, using SLECT SUM, then it compares. It then goes back to the original recordset, gets the next record, and does the SUM's again. Could this be set up as a "NESTED" Loop?? or would a Stored PRc be better. Below are the 3 passes....of course it builds the string and passes...
Step # 1
SELECT Fee_Slip.Patient_No,last_name,first_name, Fee_Slip_items.update_dt, fee_slip_items.slipitm_no
FROM ((Fee_Slip INNER JOIN fee_slip_ITEMS ON fee_slip_items.feeslip_no = fee_slip.feeslip_no)
INNER JOIN Patient ON Fee_Slip.Patient_No = Patient.Patient_No)
WHERE feeslip_post = 1 AND patient.active=1 AND (patient.deceased is Null OR patient.deceased = 0) AND left(fee_slip_items.slipitm_prd_desc, 6) <> 'VOIDED'
ORDER BY patient.last_name, patient.first_name, Patient.Patient_No, fee_slip.feeslip_no
Step # 2
SELECT SUM(fee_slip_items.slipitm_pat_total) AS Total FROM fee_slip_items WHERE fee_slip_items.slipitm_no = 163371
Step # 3
SELECT Sum(Itmtrn_Amt) AS Total FROM Fee_Slip_Items_Trans WHERE fee_slip_items_trans.slipitm_no = 163371 AND ItmTrn_Pmt_Adj IN (0, 1)
