[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-03-14
7
Medium Priority
?
226 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
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 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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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