Comments by Alexis Gaitan

Alexis Gaitan 31-May-21 10:47am View

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

Alexis Gaitan 26-May-21 12:46pm View

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

Alexis Gaitan 7-May-21 8:43am View

nevermind i just got it from your query i made some modifications and works perfect, thank you very much

Alexis Gaitan 7-May-21 8:27am View

I modified my question, as now i need yes or yes a cashamount and otherpayment columns, in order to integrate later with my systems.

Alexis Gaitan 7-May-21 8:13am View

at least im needing this:

accountcode DocEntry PaymentCode OtherPaymentAmount CashAmount sapcreditcard BPA_ProcessStatus ERP_PaymentProcessed
CWM 238 1 NULL 0.29 NULL N N
CWM 238 1 NULL 29.00 NULL N N
CWM 238 1 NULL 29.58 NULL N N
CWM 238 9 18.00 NULL 4 N N

i edited my question so to show this expected result:

accountcode DocEntry PaymentCode OtherPaymentAmount CashAmount sapcreditcard BPA_ProcessStatus ERP_PaymentProcessed
CWM 238 1 NULL 58.87 NULL N N
CWM 238 9 18.00 NULL 4 N N

Alexis Gaitan 7-May-21 8:12am View

what about im interested in other columns? like Paymentcode and StoreID

Alexis Gaitan 6-May-21 11:49am View

thank you so much

Alexis Gaitan 24-Apr-21 16:09pm View

works perfect, thank you

Alexis Gaitan 24-Apr-21 15:14pm View

thank you, one question as im using this at the beginning

DECLARE @NumInSale int

SELECT @NumInSale = NumInSale FROM OITM t1 WHERE t1.ItemCode='CID-11418272385'

IF (@NumInSale > 1)

and im using ELSE too should i add that code after ELSE statement ? and leave the other as is?

Alexis Gaitan 24-Apr-21 14:43pm View

this is the part that doesnt works

(SELECT T5.[ItemCode], T6.[U_NAME] as 'User that created Item', t5.updatedate

FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K

WHERE T5.[ItemCode] = 'CID-11418272385'

group by T5.[ItemCode], T6.[U_NAME], t5.loginstanc, t5.updatedate

having t5.loginstanc = (select min(loginstanc) from AITM where itemcode = 'CID-11418272385')),

Alexis Gaitan 24-Apr-21 14:43pm View

after the ELSE statement theres a subquery just after this line

case when t1.NumInSale > 1 THEN '1' END as uom_group_id,

--------- subquery starts
(SELECT T5.[ItemCode], T6.[U_NAME] as 'User that created Item', t5.updatedate

FROM AITM T5 inner join OUSR T6 on t5.usersign = t6.internal_K

Alexis Gaitan 24-Apr-21 14:40pm View

i just post it in What I have tried section on main post. thank you

Alexis Gaitan 24-Apr-21 14:34pm View

can you show me some example or part of it? thank you

Comments by Alexis Gaitan (Top 13 by date)