Solved

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

Posted on 2011-03-14
7
216 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
7 Comments
 
LVL 32

Expert Comment

by:ewangoya
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 40

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 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 2 61
SQL Server 2012 express 24 37
Table create permissions on SQL Server 2005 9 41
How do I subtract date and time within a same column in SQL 4 36
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…
In this article I will describe the Backup & Restore 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

775 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