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.Patien t_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.feesli p_no)
INNER JOIN Patient ON Fee_Slip.Patien t_No = Patient.Patient _No)
WHERE feeslip_post = 1 AND patient.active= 1 AND (patient.deceas ed is Null OR patient.decease d = 0) AND left(fee_slip_i tems.slipitm_pr d_desc, 6) <> 'VOIDED'
ORDER BY patient.last_na me, patient.first_n ame, Patient.Patient _No, fee_slip.feesli p_no
Step # 2
SELECT SUM(fee_slip_it ems.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_n o = 163371 AND ItmTrn_Pmt_Adj IN (0, 1)
Step # 1
SELECT Fee_Slip.Patien t_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.feesli p_no)
INNER JOIN Patient ON Fee_Slip.Patien t_No = Patient.Patient _No)
WHERE feeslip_post = 1 AND patient.active= 1 AND (patient.deceas ed is Null OR patient.decease d = 0) AND left(fee_slip_i tems.slipitm_pr d_desc, 6) <> 'VOIDED'
ORDER BY patient.last_na me, patient.first_n ame, Patient.Patient _No, fee_slip.feesli p_no
Step # 2
SELECT SUM(fee_slip_it ems.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_n o = 163371 AND ItmTrn_Pmt_Adj IN (0, 1)
Comment