Solved

top paymentid that has the same debit as another payment

Posted on 2011-03-15
18
334 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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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.
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

685 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