SQL Server Performance Forum – Threads Archive

Summing value dependant on Sale or refund

Hi all,
I have an Access query I wish to port to SQL that Sums values dependant on the transaction type being a ‘Sale’ or ‘Refund’.
The snippit I’m having trouble with is the Access statement:- Sum(IIf(InStr([transactiontype],"REF"),-[amount],[amount])) AS [Txn Value] In Access this adds a ‘-‘ to the amount field & calculates the total sales less refunds. The transact SQL query I’m running is below:- USE AT_GPSpilot
SELECT
tblSettlementLog.SettlementID AS [Sub Id],
tblSettlementLog.NetValue AS [Sub Id Value],
SUM(CASE
WHEN CHARINDEX(‘REF’,tblTransactions.transactiontype) > 0
THEN
-tblTransactions.[Amount]
ELSE
tblTransactions.[Amount]
END)
AS "Txn Value", tblSettlementLog.Created, tblSettlementLog.Sent
FROM
tblSettlementLog
INNER JOIN
tblTransactions ON tblSettlementLog.SettlementID = tblTransactions.SettlementID
WHERE
(tblSettlementLog.Created > CONVERT(DATETIME, ‘2006-01-05 00:00:00’, 102))
Group By tblSettlementLog.SettlementID,tblSettlementLog.NetValue,tblTransactions.transactiontype,
tblSettlementLog.Created,tblSettlementLog.Sent This almost get’s what I want, but it displays the positive & negative values on seperate lines instead of a single line
e.g.
Txn Value
-147607
4371269 instead of
Txn Value
4223662 Any of you out there go an idea of how to resolve this? Thanks, John

---

Multiply by -1: SUM(CASE
WHEN CHARINDEX(‘REF’,tblTransactions.transactiontype) > 0
THEN
tblTransactions.[Amount] * -1
ELSE
tblTransactions.[Amount]
END) or SUM(tblTransactions.[Amount] * CASE WHEN CHARINDEX(‘REF’,tblTransactions.transactiontype) > 0 THEN -1 ELSE 1 END)

---

The problem is, tblTransactions.transactiontype is in your GROUP BY So each transaction type is a seperate group, and will appear as a seperate row. To get it as one row you must remove transactiontype from the GROUP BY, or wrap a subsequent select around the current query, which SUMS the values

---

Well spotted, Chappy.

---

Don’t you feel a right dummy when things like this happen!!!!!!
The answer’s staring you right in the face & you don’t see it. Removing transactiontype from the GROUP BY work’s a treat. Thanks guys,
John

---

]]>