Solved

top paymentid that has the same debit as another payment

Posted on 2011-03-15
18
335 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

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.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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