add debit to case statement

select *,
case resultcode
when 'Canceled' then 'Payment Processed'
when 'Completed' then 'Payment Processed'
when 'confirmed' then 'Payment Processed'
when 'Denied' then 'Payment Processed'
when 'Pending' then 'Payment Processed'
when 'unconfirmed' then 'Payment Processed'
when '' then 'Payment Processed'
when 'Reversed' then 'Refunded'
end as resultcodetype

,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1



this query works, but I want to add another condition to resultcodetype
when resultcode='' and (debit>=0 or debit has no value) then 'Payment Processed'
when resultcode='' and debit<0 then 'Refunded'
LVL 1
rgb192Asked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Please try:

select *,
case
when resultcode = 'Canceled' then 'Payment Processed'
when resultcode = 'Completed' then 'Payment Processed'
when resultcode = 'confirmed' then 'Payment Processed'
when resultcode = 'Denied' then 'Payment Processed'
when resultcode = 'Pending' then 'Payment Processed'
when resultcode = 'unconfirmed' then 'Payment Processed'
when resultcode = '' and (debit is null or ISNUMERIC(debit) = 0 or cast(debit as money) >=0) then 'Payment Processed'
when resultcode = '' and debit is not null and ISNUMERIC(debit) = 1 and CAST(debit as money)<0 then 'Refunded'
--when resultcode = '' then 'Payment Processed'   -- I think this no longer makes sense. Please verify.
when resultcode = 'Reversed' then 'Refunded'
end as resultcodetype

,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1
0
 
Ephraim WangoyaCommented:

select *,
case
when resultcode = 'Canceled' then 'Payment Processed'
when resultcode = 'Completed' then 'Payment Processed'
when resultcode = 'confirmed' then 'Payment Processed'
when resultcode = 'Denied' then 'Payment Processed'
when resultcode = 'Pending' then 'Payment Processed'
when resultcode = 'unconfirmed' then 'Payment Processed'
when (resultcode = '' and (debit>=0 or debit is null)) then 'Payment Processed'
when (resultcode = '' and (debit < 0)) then 'Refunded'
when resultcode = '' then 'Payment Processed'
when resultcode = 'Reversed' then 'Refunded'
end as resultcodetype
0
 
wdosanjosCommented:
I think you almost provided the answer unless I'm missing something:

select *,
case resultcode
when 'Canceled' then 'Payment Processed'
when 'Completed' then 'Payment Processed'
when 'confirmed' then 'Payment Processed'
when 'Denied' then 'Payment Processed'
when 'Pending' then 'Payment Processed'
when 'unconfirmed' then 'Payment Processed'
when '' and (debit>=0 or debit is null) then 'Payment Processed'
when '' and debit<0 then 'Refunded'
--when '' then 'Payment Processed'   -- I think this no longer makes sense. Please verify.
when 'Reversed' then 'Refunded'
end as resultcodetype

,debit - convert(money, paymentfee) as 'NetAmount' from payments where isnumeric(paymentfee) = 1
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
wdosanjosCommented:
Oops. @ewangoya got it first.
0
 
ragnarok89Commented:
I believe

case
   when debit >= 0 or debit = 'null'
     then 'Payment Processed
   when debit <= 0
     then 'Refunded'

is what you need
0
 
rgb192Author Commented:
debit may be null or debit may be '' or debit may be a alphanumeric value

if this is the case, then I want resultcodetype to be 'Payment Processed'
0
 
Ephraim WangoyaCommented:

use ISNUMERIC to check whether debit is numeric or not

when ((resultcode = '') and  ((Debit is null) or (Debit = '') or (not ISNUMERIC(Debit)) ) then
0
 
rgb192Author Commented:
best answer thansk
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.