Solved

I want to left join payments y with orders o on y.orderid=o.orderid

Posted on 2011-03-08
2
293 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' from payments where dateentered between '03/01/2011' and '03/09/2011' order by dateentered desc


I want to left join payments y with orders o on y.orderid=o.orderid
orderid is int
0
Comment
Question by:rgb192
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35072633
so, you mean either:

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' 
from payments p
left join orders o
  on  y.orderid=o.orderid
where p.dateentered between '03/01/2011' and '03/09/2011' 
order by p.dateentered desc 

Open in new window

but that seems "trivial" as question ...

so, possibly 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' 
from orders o
left join payments p
  on  y.orderid=o.orderid
 and p.dateentered between '03/01/2011' and '03/09/2011' 
order by p.dateentered desc 

Open in new window


but that does not make sense to apply left join.
either you want all orders with payments in that date range (inner join), or all order made in that date range, even if there is no payments (at all. ..)

so, something like:

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' 
from orders o
left join payments p
  on  y.orderid=o.orderid
where o.dateentered between '03/01/2011' and '03/09/2011' 
order by o.dateentered desc 

Open in new window


note that the WHERE clause now checks on orders table date column

hope this helps
0
 

Author Closing Comment

by:rgb192
ID: 35201207
works great, 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

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…
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
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…

809 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