Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

add debit to case statement

Posted on 2011-03-07
8
Medium Priority
?
255 Views
Last Modified: 2012-05-11
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'
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35060703

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
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35060712
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
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35060746
Oops. @ewangoya got it first.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:ragnarok89
ID: 35060761
I believe

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

is what you need
0
 

Author Comment

by:rgb192
ID: 35060806
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
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35061017

use ISNUMERIC to check whether debit is numeric or not

when ((resultcode = '') and  ((Debit is null) or (Debit = '') or (not ISNUMERIC(Debit)) ) then
0
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 35061473
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
 

Author Closing Comment

by:rgb192
ID: 35192275
best answer thansk
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question