Solved

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

Posted on 2011-03-14
7
215 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
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.

 
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

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 59
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 40
How can I exclude some wording in a like statement? 39 65
Strange msg in the SSMS pane 13 48
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
This is a video describing the growing solar energy use in Utah. This is a topic that greatly interests me and so I decided to produce a video about it.

914 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

17 Experts available now in Live!

Get 1:1 Help Now