simplifying stored proc - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

simplifying stored proc

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-06-18 : 16:31:59

How can I rewrite these queries into one stored proc so it is easier to read and performs better?

SELECT txtAccountNum, dteCycleDate, SUM(curTransacAmnt) as RegVisa_Total
FROM Transactions
WHERE (txtMemoFlag = 'M' AND txtTransacCode = '10') AND txtAccountNum = '0000000000122796' AND dteCycleDate = '11/20/2000'
GROUP BY txtAccountNum, dteCycleDate

SELECT txtAccountNum, dteCycleDate, SUM(curTransacAmnt) as VisaPayment_Total
FROM Transactions
WHERE (txtMemoFlag in ('M','S') AND txtTransacCode = '31') AND txtAccountNum = '0000000000122796' AND dteCycleDate = '11/20/2000'
GROUP BY txtAccountNum, dteCycleDate

SELECT txtAccountNum, dteCycleDate, SUM(curTransacAmnt) as Split_Total
FROM Transactions
WHERE (txtMemoFlag = 'S' AND txtTransacCode <> '31') AND txtAccountNum = '0000000000122796' AND dteCycleDate = '11/20/2000'
GROUP BY txtAccountNum, dteCycleDate

SELECT txtAccountNum, dteCycleDate, SUM(curTransacAmnt) as Reimbursements
FROM Transactions
WHERE (txtMemoFlag = 'MP' OR txtMemoFlag = 'SP') AND txtAccountNum = '0000000000122796' AND dteCycleDate = '11/20/2000'
GROUP BY txtAccountNum, dteCycleDate

SELECT txtAccountNum, dteCycleDate, SUM(curTransacAmnt) as OOP_Total
FROM Transactions
WHERE ((txtMemoFlag = '$' OR txtMemoFlag = 'S') AND txtTransacCode IS NULL) AND txtAccountNum = '0000000000122796' AND dteCycleDate = '11/20/2000'
GROUP BY txtAccountNum, dteCycleDate

Also, which one is better?
txtMemoFlag in ('M','S') OR
txtMemoFlag = 'M' OR txtMemoFlag = 'S'

Thanks for your help.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-18 : 16:50:04

SELECT txtAccountNum, dteCycleDate,
SUM(case when txtMemoFlag = 'M' AND txtTransacCode = '10') then curTransacAmnt else 0 end) as RegVisa_Total ,
SUM(case when txtMemoFlag in ('M','S') AND txtTransacCode = '31') then curTransacAmnt else 0 end) as VisaPayment_Total ,
...
FROM Transactions
WHERE (txtMemoFlag in ('M', 'S', 'MP',...)
AND txtAccountNum = '0000000000122796'
AND dteCycleDate = '11/20/2000'
GROUP BY txtAccountNum, dteCycleDate

Also, which one is better?
txtMemoFlag in ('M','S') OR
txtMemoFlag = 'M' OR txtMemoFlag = 'S'

Will probably get the same query plan - check in query analyser
The in statement is usually clearer though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-18 : 16:53:45

I would say limit the number of times you go to the transaction table....

How about


  SELECT txtAccountNum, dteCycleDate, curTransacAmnt, txtMemoFlag, txtTransacCode
    INTO #Tran_Temp
    FROM Transaction
   WHERE txtAccountNum = '0000000000122796' AND dteCycleDate = '11/20/2000'

  SELECT txtAccountNum, dteCycleDate, SUM(curTransacAmnt) as RegVisa_Total 
    FROM #Trans_Temp 
   WHERE txtMemoFlag = 'M' AND txtTransacCode = '10'
GROUP BY txtAccountNum, dteCycleDate

Ect...just UNION them together, and add a "Type" field to identify which amount is which..

Brett

8-)

EDIT: Nevermind...go with Nigels....or better yet, do a show plan and see what's better/faster...

Edited by - x002548 on 06/18/2003 16:55:33

Edited by - x002548 on 06/18/2003 16:57:02

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources