Subtraction Woes-- SQL Studs or Studettes Help Needed!!!

Question

Post reply

Subtraction Woes-- SQL Studs or Studettes Help Needed!!!

davidsalazar01

SSCarpal Tunnel

Points: 4072
More actions
December 24, 2008 at 11:37 am

#204471

Hello All :D,
The requirement for this query to work is the following:
The data in the db is credit card transaction data and each record is a unique row.
There's a hardcoded "Credit" parameter in the Trans_Type_ID column of the view I'm using that needs to summed up but subtracted as a negative amount from the summed up positive value from the code below in order to subtract the credit/return amounts from the total summary totals. When I run the code below it gives me what I want as far as summed up monthly totals, but the problem i'm having is modifying the code to deduct the credit (negative) amounts from the total summed amount for each month. I just can't figure out how to do that. Essentially the end result of this query is to subtract Trans_Type_ID = "Credit" summed and to subtract that amount from the total amount. If you need more clarity, let me know. 🙂 🙂 🙂
Here's the code that I'm using to pull data from a view I have:
select CONVERT(CHAR(7), Settle_Date_DT, 121), COUNT(*), SUM(Total_Amt_MN) from TrxDetailCard A (NOLOCK)
LEFT OUTER JOIN TRX_Invoice_T I (NOLOCK)
ON A.TRX_HD_Key = I.TRX_HD_Key
WHERE (A.Payment_Type_ID IN ('AMEX', 'DISCOVER', 'MASTERCARD', 'VISA', 'DEBIT', 'EBT', 'EGC')) -- 'EGC','LOYALTY'
AND (ISNULL(A.Result_CH, '99999') = '0')
AND (ISNULL(A.Void_Flag_CH, '0') <> '1')
AND (A.Trans_Type_ID NOT IN ('Reversal', 'Void', 'Credit'))--
AND (A.Trans_Type_ID <> 'Reversal')
AND (A.Settle_Date_DT >= '2008-01-01' AND A.Settle_DATE_DT < '2009-01-01')
AND (A.Merchant_Key = 4884)
AND (A.Settle_Flag_CH = 1)
GROUP BY CONVERT(CHAR(7), Settle_Date_DT, 121)
ORDER BY 1

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think what you are looking for is using a CASE and sum together to get the value you are after;

here's a the core example:

[font="Courier New"]SUM(CASE WHEN A.Trans_Type_ID IN ('Reversal', 'Void', 'Credit')

THEN (Total_Amt_MN * -1)

ELSE Total_Amt_MN

END) AS TOTALAMT[/font]

here's what i figure you'd want, removing part of the WHERE statment so you don't exclude the reversals

[font="Courier New"]

SELECT

CONVERT(CHAR(7), Settle_Date_DT, 121) AS SETTLEDATE,

COUNT(*) AS TOTALCOUNT,

SUM(CASE WHEN A.Trans_Type_ID IN ('Reversal', 'Void', 'Credit')

THEN (Total_Amt_MN * -1)

ELSE Total_Amt_MN

END) AS TOTALAMT

FROM TrxDetailCard A (NOLOCK)

LEFT OUTER JOIN TRX_Invoice_T I (NOLOCK)

ON A.TRX_HD_Key = I.TRX_HD_Key

WHERE (A.Payment_Type_ID IN ('AMEX', 'DISCOVER', 'MASTERCARD', 'VISA', 'DEBIT', 'EBT', 'EGC')) -- 'EGC','LOYALTY'

AND (ISNULL(A.Result_CH, '99999') = '0')

AND (ISNULL(A.Void_Flag_CH, '0') <> '1')

AND (A.Settle_Date_DT >= '2008-01-01' AND A.Settle_DATE_DT < '2009-01-01')

AND (A.Merchant_Key = 4884)

AND (A.Settle_Flag_CH = 1)

GROUP BY CONVERT(CHAR(7), Settle_Date_DT, 121)

ORDER BY 1

[/font]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

davidsalazar01 SSCarpal Tunnel Points: 4072 More actions · Answer 2

Thanks for the response and I apologize for getting back to you so much after the original posting. Holidays...uhgg. Now when I run the query it seems to give me the correct total amount most of the time but the transaction counts are always off. Any idea why this might be happening?

Thanks! 😀