Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 230
  • Last Modified:

if o.dateentered (datetime) does not exist then use 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 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
rgb192
Asked:
rgb192
1 Solution
 
Ephraim WangoyaCommented:
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
 
tigin44Commented:
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
 
rgb192Author Commented:

y.dateentered always exists



o.dateentered is an additional column

do not use o.dateentered in the between date1 and date2
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
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
 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now