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.
| Author |
Topic |
|
PeterG
Posting Yak Master
156 Posts |
 Posted - 2003-07-23 : 17:35:40
|
I thought with this stored proc, I'd only get one row of recordset. Running the Sp, I get three rows: one for cycledate = @dteStartdate, another for cycledate = @fixed, and another for cycledate = NULL.Sample recordset:txtAccountNum dteCycleDate RegTotal PaymentTotal SplitTotal Reimbursements OOPTotal 110291 NULL 13541.0200 .0000 .0000 .0000 .0000110291 1963-01-01 .0000 .0000 .0000 .0000 100.0000110291 2003-04-28 6641.0600 .0000 .0000 .0000 .0000How can I rewrite this so that I'd only get one row giving me all the totals for account number and al the cycledates specified in the where clause (i.e., RegTotal should be 13541.0200 plus 6641.0600 and SplitTotal should be 100.0000)?SELECT txtAccountNum, dteCycleDate, SUM (case when txtMemoFlag = 'M' AND txtTransacCode in ('10','P','A', '20', '22','50','52','11','C','D') then curTransacAmnt else 0 end) as Reg_Total,SUM (case when txtMemoFlag in ('M','S') AND txtTransacCode in ('31', 'Y') then curTransacAmnt else 0 end) as Payment_Total, SUM (case when txtMemoFlag = 'S' AND txtTransacCode NOT IN ('31', 'Y') then curTransacAmnt else 0 end) as Split_Total,SUM (case when txtMemoFlag IN ('MP', 'SP') then curTransacAmnt else 0 end) as Reimbursements,SUM (case when txtMemoFlag IN ('$', 'S') AND txtTransacCode IS NULL then curTransacAmnt else 0 end) as OOP_TotalFROM TransactionsWHERE (txtAccountNum = @txtAccountNumber AND (dteCycleDate = @dteStartDate or dteCycleDate = @Fixed or dteCycleDate is NULL))GROUP BY txtAccountNum, dteCycleDate |
|
|
MichaelP
Jedi Yak
2489 Posts |
|
|
|
|
|
|
|
totals in one row