Click here to Skip to main content
15,035,723 members
Please Sign up or sign in to vote.
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
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900