By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,412 Members | 1,630 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,412 IT Pros & Developers. It's quick & easy.

Multiple SUMS SQL Query

P: 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)
Feb 3 '09 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
You might be able to get away with the three queries by using CASE..WHEN..

-- CK
Feb 3 '09 #2

Post your reply

Sign in to post your reply or Sign up for a free account.