Solved

top paymentid that has the same debit as another payment

Posted on 2011-03-15
18
328 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
Post some sample data with expected result.
0
 

Author Comment

by:rgb192
Comment Utility

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 40

Expert Comment

by:Sharath
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
i need to guarentee that the payment selected is 'completed' resultcode
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:rgb192
Comment Utility
that query returned another orderid

I need orderid=8
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
>> 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
Comment Utility
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 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
okay, you are correct
72660
is top completed paymentid
0
 
LVL 40

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
where do i add that I only want to do this for payments.orderid=7001951
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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
Comment Utility
thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now