Subtract SQL query two diff columns and records

Question

0.00/5 (No votes)

See more:

Hi i have the following sql query:

SELECT 
  CASE
    WHEN StoreID = 1 THEN 'CWM'
    WHEN StoreID = 2 THEN 'CWD'
  END
    AS accountcode,
  DocEntry,
  PaymentCode,
  ABS(SUM(CASE WHEN PaymentCode <> 1 THEN paymentamount END))  AS OtherPaymentAmount,
  SUM(CASE WHEN PaymentCode  = 1 THEN paymentamount END)  AS CashAmount,
   abs(SUM(CASE WHEN PaymentCode  = 11 THEN paymentamount END))  AS creditnoteamount,
  ABS(SUM(CASE WHEN PaymentCode <> 1 THEN paymentamount 
   WHEN PaymentCode  = 1 THEN paymentamount
  END)) as TotalAmount,

  tenders.sapcreditcard,
  FileName,
  BPA_ProcessStatus,
  ERP_PaymentProcessed
FROM
  [Plu].[dbo].[payments_header]
INNER JOIN
  tenders
    ON payments_header.PaymentCode = tenders.postenderid


GROUP BY
  CASE
    WHEN StoreID = 1 THEN 'CWM'
    WHEN StoreID = 2 THEN 'CWD'
  END,
  DocEntry,
  PaymentCode,
  tenders.sapcreditcard,
  FileName,
  BPA_ProcessStatus,
  ERP_PaymentProcessed

and im getting these results:

accountcode	DocEntry	PaymentCode	OtherPaymentAmount	CashAmount	creditnoteamount	TotalAmount	sapcreditcard	FileName	BPA_ProcessStatus	ERP_PaymentProcessed
CWM	332	1	NULL	6757.26	NULL	6757.26	NULL	TX.1_20210515.xml	N	N
CWM	332	11	90.29	NULL	90.29	90.29	28	TX.1_20210515.xml	N	N
CWM	332	6	8989.97	NULL	NULL	8989.97	1	TX.1_20210515.xml	N	N
CWM	332	7	4514.50	NULL	NULL	4514.50	2	TX.1_20210515.xml	N	N

i need to subtract the following columns attached image Caasfasfasfsafpture — ImgBB[^]

the result of otherpayment sum of paymentcode = 11 with paymentcode =1 column cashsum

any tip to achieve this?

thank you

What I have tried:

SELECT 
  CASE
    WHEN StoreID = 1 THEN 'CWM'
    WHEN StoreID = 2 THEN 'CWD'
  END
    AS accountcode,
  DocEntry,
  PaymentCode,
  ABS(SUM(CASE WHEN PaymentCode <> 1 THEN paymentamount END))  AS OtherPaymentAmount,
  SUM(CASE WHEN PaymentCode  = 1 THEN paymentamount END)  AS CashAmount,
   abs(SUM(CASE WHEN PaymentCode  = 11 THEN paymentamount END))  AS creditnoteamount,
  ABS(SUM(CASE WHEN PaymentCode <> 1 THEN paymentamount 
   WHEN PaymentCode  = 1 THEN paymentamount
  END)) as TotalAmount,

  tenders.sapcreditcard,
  FileName,
  BPA_ProcessStatus,
  ERP_PaymentProcessed
FROM
  [Plu].[dbo].[payments_header]
INNER JOIN
  tenders
    ON payments_header.PaymentCode = tenders.postenderid


GROUP BY
  CASE
    WHEN StoreID = 1 THEN 'CWM'
    WHEN StoreID = 2 THEN 'CWD'
  END,
  DocEntry,
  PaymentCode,
  tenders.sapcreditcard,
  FileName,
  BPA_ProcessStatus,
  ERP_PaymentProcessed

Posted 26-May-21 3:27am

Alexis Gaitan

Add a Solution

Comments

SeanChupas 26-May-21 12:05pm

Not sure what you are asking but to subtract 2 columns all you have to do is column1 - column2.

Alexis Gaitan 26-May-21 12:46pm

accountcode DocEntry PaymentCode OtherPaymentAmount CashAmount creditnoteamount TotalAmount sapcreditcard FileName BPA_ProcessStatus ERP_PaymentProcessed
CWM 332 1 NULL 6757.26 NULL 6757.26 NULL TX.1_20210515.xml N N
CWM 332 11 90.29 NULL 90.29 90.29 28 TX.1_20210515.xml N N
CWM 332 6 8989.97 NULL NULL 8989.97 1 TX.1_20210515.xml N N
CWM 332 7 4514.50 NULL NULL 4514.50 2 TX.1_20210515.xml N N

i need to subtract the column cashamount with record paymentcode =1 and column creditnoteamount paymentcode = 11 and combine it in to one record

SeanChupas 26-May-21 13:23pm

SELECT ..., ((SELECT statment to get cash amount) - (SELECT statement to get credit note amount)) AS Difference.

Something like that will work.

Alexis Gaitan 31-May-21 10:47am

need to get something like this, i need to subtract 6757.26 - 90.29

accountcode DocEntry PaymentCode OtherPaymentAmount CashAmount TotalAmount sapcreditcard FileName BPA_ProcessStatus ERP_PaymentProcessed
CWM 332 1 NULL 6,666.97 6,666.97 NULL TX.1_20210515.xml N N
CWM 332 6 8989.97 NULL 8989.97 1 TX.1_20210515.xml N N
CWM 332 7 4514.5 NULL 4514.5 2 TX.1_20210515.xml N N

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)