Solved

add debit to case statement

Posted on 2011-03-07
8
253 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 500 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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

726 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