rgb192
asked on
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
if o.dateentered (datetime) does not exist then use y.dateentered
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
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
ASKER
y.dateentered always exists
o.dateentered is an additional column
do not use o.dateentered in the between date1 and date2
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks
Open in new window