Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

top paymentid that has the same debit as another payment

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
rgb192
Asked:
rgb192
  • 8
  • 7
  • 2
  • +1
1 Solution
 
KGNicklCommented:
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
 
qasim_mdCommented:
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
 
qasim_mdCommented:
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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
SharathData EngineerCommented:
Post some sample data with expected result.
0
 
rgb192Author Commented:

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
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
i need to guarentee that the payment selected is 'completed' resultcode
0
 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
that query returned another orderid

I need orderid=8
0
 
SharathData EngineerCommented:
>> 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
 
rgb192Author Commented:
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
 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
okay, you are correct
72660
is top completed paymentid
0
 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
where do i add that I only want to do this for payments.orderid=7001951
0
 
SharathData EngineerCommented:
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
 
rgb192Author Commented:
thanks
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now