totals in one row - 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)

totals in one row

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-07-23 : 17:36:25

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 .0000
110291 1963-01-01 .0000 .0000 .0000 .0000 100.0000
110291 2003-04-28 6641.0600 .0000 .0000 .0000 .0000

How 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_Total
FROM Transactions
WHERE (txtAccountNum = @txtAccountNumber AND (dteCycleDate = @dteStartDate or dteCycleDate = @Fixed or dteCycleDate is NULL))
GROUP BY txtAccountNum, dteCycleDate

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-23 : 18:12:38

which of the 3 dteCycleDate do you want in the resultset?

SELECT txtAccountNum, dteCycleDate = max(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_Total
FROM Transactions
WHERE (txtAccountNum = @txtAccountNumber AND (dteCycleDate = @dteStartDate or dteCycleDate = @Fixed or dteCycleDate is NULL))
GROUP BY txtAccountNum

==========================================
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.

PeterG
Posting Yak Master

156 Posts

Posted - 2003-07-23 : 18:28:44

Thanks nr.

I get the desired results executing the SP you modified. The cycledate doesn't really matter as all I'm after are the totals. I get this warning though: "Warning: Null value eliminated from aggregate." What does this mean?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources