Solved

if o.dateentered (datetime) does not exist  then use y.dateentered

Posted on 2011-03-14
7
218 Views
Last Modified: 2012-05-11
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 = 'Canceled_Reversal' then 'Refunded' when resultcode = 'Reversed' then 'Refunded' end as resultcodetype ,debit - convert(money, paymentfee) as 'netamount',y.email as pemail from payments y left join orders o on o.orderid=y.orderid where (o.statusid!=8 or o.statusid is null) and dateentered between '09/01/2010' and '03/15/2011' and ((y.orderid=0) or (itemid=0 and y.orderid>0) or 1=0 or 1=0) order by dateentered desc


if o.dateentered (datetime) does not exist  then use y.dateentered


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
7 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35133936
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 = 'Canceled_Reversal' then 'Refunded' 
	when resultcode = 'Reversed' then 'Refunded' end as resultcodetype ,
	debit - convert(money, paymentfee) as 'netamount',
	y.email as pemail 
from payments y 
left join orders o on o.orderid=y.orderid 
where (o.statusid!=8 or o.statusid is null) 
and case 
      when o.dateentered IS null then 
        y.dateentered
      else
        o.dateentered
      end
      between '09/01/2010' and '03/15/2011' 
and ((y.orderid=0) or (itemid=0 and y.orderid>0) or 1=0 or 1=0) 
order by case when o.dateentered IS null then y.dateentered else o.dateentered end desc

Open in new window

0
 
LVL 26

Expert Comment

by:tigin44
ID: 35133938
use ISNULL as

ISNULL(o.dateentered, y.dateentered)

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 = 'Canceled_Reversal' then 'Refunded'
                        when resultcode = 'Reversed' then 'Refunded' end as resultcodetype ,
                  debit - convert(money, paymentfee) as 'netamount',y.email as pemail
from payments y
      left join orders o on o.orderid=y.orderid
where (o.statusid!=8 or o.statusid is null) and ISNULL(o.dateentered, y.dateentered)  between '09/01/2010' and '03/15/2011' and ((y.orderid=0) or (itemid=0 and y.orderid>0) or 1=0 or 1=0)
order by dateentered desc
0
 

Author Comment

by:rgb192
ID: 35134288

y.dateentered always exists



o.dateentered is an additional column

do not use o.dateentered in the between date1 and date2
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 41

Expert Comment

by:Sharath
ID: 35134311
Are you looking for this?
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 = 'Canceled_Reversal' THEN 'Refunded' 
         WHEN resultcode = 'Reversed' THEN 'Refunded' 
       END                                AS resultcodetype, 
       debit - CONVERT(MONEY, paymentfee) AS 'netamount', 
       y.email                            AS pemail 
  FROM payments y 
       LEFT JOIN orders o 
         ON o.orderid = y.orderid 
 WHERE ( o.statusid != 8 
          OR o.statusid IS NULL ) 
       AND ( ( o.dateentered BETWEEN '09/01/2010' AND '03/15/2011' ) 
              OR ( y.dateentered BETWEEN '09/01/2010' AND '03/15/2011' ) ) 
       AND ( ( y.orderid = 0 ) 
              OR ( itemid = 0 
                   AND y.orderid > 0 ) 
              OR 1 = 0 
              OR 1 = 0 ) 
 ORDER BY dateentered DESC 

Open in new window

0
 

Author Comment

by:rgb192
ID: 35141854
y.dateentered always exists



o.dateentered is an additional column

do not use o.dateentered in the between date1 and date2


maybe
(if o.dateordered doesnt exist use y.dateentered) as date
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35142237
You need a CASE statement. try this.
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 = 'Canceled_Reversal' THEN 'Refunded' 
           WHEN resultcode = 'Reversed' THEN 'Refunded' 
         END AS resultcodetype, 
         debit - CONVERT(MONEY,paymentfee) AS 'netamount', 
         y.email                           AS pemail 
    FROM payments y 
         LEFT JOIN orders o 
           ON o.orderid = y.orderid 
   WHERE (o.statusid != 8 
           OR o.statusid IS NULL) 
         AND (CASE 
                WHEN o.dateentered BETWEEN '09/01/2010' AND '03/15/2011' THEN o.dateentered 
                ELSE y.dateentered 
              END BETWEEN '09/01/2010' AND '03/15/2011') 
         AND ((y.orderid = 0) 
               OR (itemid = 0 
                   AND y.orderid > 0) 
               OR 1 = 0 
               OR 1 = 0) 
ORDER BY dateentered DESC

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 35149740
thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Help with simplifying SQL 6 54
SQL query with cast 38 60
Help Required 2 46
Applying Roles in Common Scenarios 3 19
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

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