Solved

top paymentid that has the same debit as another payment

Posted on 2011-03-15
18
337 Views
Last Modified: 2012-06-27
CREATE TABLE [dbo].[payments](
      [paymentid] [int] IDENTITY(1,1) NOT NULL,
      [orderid] [int] NOT NULL,
      [dateentered] [datetime] NULL,
      [datebilled] [datetime] NULL,
      [type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [number] [varchar](80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [expiration] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [CV2] [int] NOT NULL,
      [confirmation] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [resultcode] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
      [debit] [money] NOT NULL,
      [paymentinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ebayquantity] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [paymentfee] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [email] [varchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_payments] PRIMARY KEY CLUSTERED
(
      [paymentid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]


select * from payments where resultcode='confirmed' and orderid=8 and paymentid is top paymentid

I want to select the top paymentid that is  resultcode='confirmed' and orderid=8  and there is another payment with the same orderid that has the same debit and is resultcode 'completed'

not the top paymentid overall
0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 5

Expert Comment

by:KGNickl
ID: 35139868
Not sure what you mean by top paymentid overall. Do you mean highest paymentid? Most recent dateentered? Most recent datebilled? Some other condition?

I think an explanation of what your trying to find and why might help.

But I'm thinking your solution might be a basic query that returns a result set and then some script to iterate through the result set that is returned from the query to give you the solution your looking for. I could be totally off on this or just misread as well.
0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35140090
Try this:

select max(paymentid) from
(select P1.paymentid as paymentid from payments P1,
      (select paymentid as paymentid from payments where resultcode='completed' and orderid=8 ) P2
where P1.paymentid = P2.paymentid
and P1.orderid= P2.orderid
and P1.resultcode='confirmed' and P1.orderid=8) SQ1
0
 
LVL 4

Expert Comment

by:qasim_md
ID: 35140097
Try this:

select max(paymentid) from
(select P1.paymentid as paymentid from payments P1,
      (select paymentid as paymentid from payments where resultcode='completed' and orderid=8 ) P2
where P1.paymentid = P2.paymentid
and P1.orderid= P2.orderid
and P1.resultcode='confirmed' and P1.orderid=8) SQ1

did this help ?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:Sharath
ID: 35140326
Post some sample data with expected result.
0
 

Author Comment

by:rgb192
ID: 35149731

paymentid   orderid              type         resulcode              debit
67951      7001951      Paypal                            0.00
67952      7001951      Visa                            0.00
72635      7001951      Paypal      Completed      46.00
72655      7001951      Paypal      Completed      268.00
72656      7001951      Paypal      Confirmed      268.00
72660      7001951      Paypal      Completed      268.00


only select paymentid 72655 because it is the top paymentid that has a matching row of confirmed resultcode that has an equal debit
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35151710
try this.
SELECT * 
  FROM payments p1 
 WHERE p1.resultcode <> 'confirmed' 
       AND p1.debit = (SELECT TOP 1 p2.debit 
                         FROM payments p2 
                        WHERE p2.resultcode = 'confirmed' 
                              AND p2.orderid = 8)

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 35151717
sorry, this one.
SELECT TOP 1 * 
    FROM payments p1 
   WHERE p1.resultcode <> 'confirmed' 
         AND p1.debit = (SELECT TOP 1 p2.debit 
                           FROM payments p2 
                          WHERE p2.resultcode = 'confirmed' 
                                AND p2.orderid = 8) 
ORDER BY paymentid DESC

Open in new window

0
 

Author Comment

by:rgb192
ID: 35152475
i need to guarentee that the payment selected is 'completed' resultcode
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35152828
then you can change the filter condition.
SELECT TOP 1 * 
    FROM payments p1 
   WHERE p1.resultcode <> 'completed' 
         AND p1.debit = (SELECT TOP 1 p2.debit 
                           FROM payments p2 
                          WHERE p2.resultcode = 'confirmed' 
                                AND p2.orderid = 8) 
ORDER BY paymentid DESC

Open in new window

0
 

Author Comment

by:rgb192
ID: 35259453
that query returned another orderid

I need orderid=8
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35264040
>> I need orderid=8

Hard to tweak the query without some sample data. Provide some data with expected result. The sample data in http:#35149731 is not sufficient to tweak the query.
0
 

Author Comment

by:rgb192
ID: 35298477
paymentid   orderid              type         resulcode              debit
67951      7001951      Paypal                                        0.00
67952      7001951      Visa                                            0.00
72635      7001951      Paypal      Completed               46.00
72655      7001951      Paypal      Completed             268.00
72656      7001951      Paypal      Confirmed              268.00
72660      7001951      Paypal      Completed              268.00

74444          7019304    Mastercard                                  268.00




only select paymentid 72655 because it is the top paymentid that has a matching row of confirmed resultcode that has an equal debit


your query is selecting 74444 which is another orderid (from another order)



SELECT TOP 1 *
    FROM payments p1
   WHERE p1.resultcode <> 'completed'
         AND p1.debit = (SELECT TOP 1 p2.debit
                           FROM payments p2
                          WHERE p2.resultcode = 'confirmed'
                                AND p2.orderid = 7001951)
ORDER BY paymentid DESC
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35300831
72660 is the top completed payment id which has same debit (268.00) as 72656 (confirmed paymentid with debit 268.00). Why don't you want 72660? why 72655?
0
 

Author Comment

by:rgb192
ID: 35301497
okay, you are correct
72660
is top completed paymentid
0
 
LVL 41

Expert Comment

by:Sharath
ID: 35302475
check this.
SELECT TOP 1 * 
    FROM payments t1 
   WHERE t1.orderid = (  SELECT TOP 1 t2.orderid 
                           FROM payments t2 
                          WHERE resulcode = 'Confirmed' 
                                AND t1.debit = t2.debit 
                                AND t1.orderid = t2.orderid 
                       ORDER BY t2.paymentid DESC) 
         AND t1.resulcode = 'Completed' 
ORDER BY t1.paymentid DESC

Open in new window

0
 

Author Comment

by:rgb192
ID: 35310036
where do i add that I only want to do this for payments.orderid=7001951
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 35319245
try this.
SELECT TOP 1 * 
    FROM payments t1 
   WHERE t1.orderid = (  SELECT TOP 1 t2.orderid 
                           FROM payments t2 
                          WHERE resulcode = 'Confirmed' 
                                AND t1.debit = t2.debit 
                                AND t1.orderid = t2.orderid 
                       ORDER BY t2.paymentid DESC) 
         AND t1.resulcode = 'Completed' 
         AND t1.orderid=7001951
ORDER BY t1.paymentid DESC

Open in new window

0
 

Author Closing Comment

by:rgb192
ID: 35319301
thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

705 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