Solved

add debit to case statement

Posted on 2011-03-07
8
249 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 32

Expert Comment

by:ewangoya
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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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:ewangoya
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql query 12 67
Testing connection to sql 7 58
Get row count of current SQL query 8 45
how to eliminate duplicates in a string variable in t-sql? 30 63
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now