CODEWITHSUNDEEP
sql-serversql-server-2008
Baral
Baral

Reputation: 3141

Find highest transaction amount after any canceled transaction

I have a table containing millions of transactions. I need to find the highest paid current amount. "Current" is defined as occurring after the last canceled transaction. The table is as follows:

Id (guid)    ServiceId   CreatedDate               AmountPaid    InsurerId   IsCanceled
76E9A3...    19          2013-08-30 12:34:01.580   56.00         96          0
C3F325...    19          2013-08-30 12:34:02.069   14.95         110         0
96E9A3...    19          2013-08-30 12:32:01.540   109.00        95          1
C3BC25...    19          2013-08-30 12:32:02.007   15.95         108         1
85E9A3...    19          2013-08-30 12:30:01.701   101.00        95          0
A3F325...    19          2013-08-30 12:30:02.069   13.95         108         0

As you can see, for the same serviceID, I have multiple transactions. What I need to retrieve here is the $56 transaction, because it is the highest for that ServiceId after the most recent canceled transaction for that same ServiceID.

If I do:

 ORDER BY CreatedDate DESC, AmountPaid DESC

The 1st row would be the 14.95$ transaction...

If I do:

ORDER BY AmountPaid DESC, CreatedDate DESC

The 1st row would be the 101$ transaction

MORE INFO:

Once a service transaction is canceled, any transaction become invalid. The only transactions valid are the one(s) created after the canceled transactions.

Upvotes: 0

Views: 75

Answers (2)

Tim Schmelter
Tim Schmelter

Reputation: 460288

You can use a ROW_NUMBER and a CTE:

WITH CTE AS
(
    SELECT t1.Id, t1.ServiceId, t1.CreatedDate, t1.AmountPaid,  t1.InsurerId, t1.IsCanceled,
        RN = ROW_NUMBER() OVER ( PARTITION BY t1.ServiceId
                                 ORDER BY t1.AmountPaid DESC, t1.CreatedDate DESC )
    FROM dbo.Transactions t1
    WHERE  t1.iscanceled = 0 
    AND (NOT EXISTS(SELECT 1 FROM dbo.transactions t2 
                    WHERE  t1.serviceid = t2.serviceid 
                    AND    t2.iscanceled = 1) 
     OR (t1.createddate > (
                   SELECT Max(createddate) 
                   FROM   dbo.transactions t2 
                   WHERE  t1.serviceid = t2.serviceid 
                   AND t2.iscanceled = 1)))
)
SELECT Id, ServiceId, CreatedDate, AmountPaid,  InsurerId, IsCanceled
FROM CTE
WHERE RN = 1

Demo

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280625

;WITH m AS
(
  SELECT ServiceID, m = MAX(CreatedDate) 
  FROM dbo.whatever 
  WHERE IsCanceled = 1 GROUP BY ServiceID
),
n AS
(
  SELECT w.*, 
    rn = ROW_NUMBER() OVER (PARTITION BY w.ServiceID ORDER BY w.AmountPaid DESC)
  FROM dbo.whatever AS w
  LEFT OUTER JOIN m ON w.ServiceID = m.ServiceID
  WHERE w.CreatedDate > COALESCE(m.m, '19000101')
)
SELECT * FROM n WHERE rn = 1;

Upvotes: 5

Related Questions