Finding the latest unreversed receipt

1

Hi There.
I have tried to get the solution from ChatGPT but it has not worked. I think I need real people to help.
I have a transaction table made up of
RecordId Int
MatterId Int
Date DateTime
Amount Decimal(13.2)

I need a script that will by MatterId, Date find the latest positive transaction per MatterId of a Select list.
If the latest transaction is a Positive then look no further
If the latest transaction is a Negative look for the previous positive transaction of the same amount by Date desc, RecordId desc and exclude those two transactions, then repeat the procedure until a Positive transaction is found then stop.

The following are the two tables:
Matter Table
RecordId FileRef
12599 AMA5/0126

Transaction Table
RecordId MatterId Date Amount
1 12599 05/12/2023 88.50
2 12599 05/12/2023 -88.50
3 12599 05/01/2024 88.50
4 12599 05/02/2024 88.50
5 12599 05/03/2024 88.50
6 12599 04/04/2024 88.50
7 12599 06/05/2024 88.50
8 12599 04/06/2024 88.50
9 12599 28/06/2024 -88.50
10 12599 28/06/2024 -88.50

Select
m.FileRef,
cd.Date,
cd.Amount
From Matter m
Left Join ColDebit cd On cd.CD_MatterId = m.M_RecordId

I want to change the Left Join ColDebit to look for the latest positive transaction. If the latest transaction is negative it must be matched against the latest positive and removed first.

e.g.
The last Transaction 10 is negative so it must be matched against Transaction 8 because it is the first positive transaction.
Transaction 9 is negative so it must be matched against 7 because it is the first positive transaction after the previous transaction has been ellimated.
The result for this Matter 12599 should be transaction 6 which is dated 04/04/2024 and amount is 88.50.

2

You should ask ChatGTP to make a running total for amount and partitioned it by MatterId and sort by Date DESCENDING. If that doesn't work you can always use Google to find some documentation about running total.

You're doing great to use new technology!

3

Could you provide code to CREATE and load (INSERT) the tables?

4

IF OBJECT_ID('[Matter]', 'U') IS NOT NULL DROP TABLE [Matter];

CREATE TABLE [dbo].[Matter](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
NOT NULL,
CONSTRAINT [PK_Matter] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO dbo.Matter(FileRef)
VALUES
('AMA5/0001'),
('AMA5/0002'),
('AMA5/0003');
GO

IF OBJECT_ID('[ColDebit]', 'U') IS NOT NULL DROP TABLE [ColDebit];

CREATE TABLE [dbo].[ColDebit](
[RecordId] [int] IDENTITY(1,1) NOT NULL,
[MatterId] [int] NOT NULL,
[Date] [datetime] NOT NULL,
[Amount] Decimal NOT NULL,
CONSTRAINT [PK_ColDebit] PRIMARY KEY CLUSTERED
(
[RecordId] ASC
) WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 90
) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO dbo.ColDebit(MatterId, Date, Amount)
VALUES
(1, '05/12/2023', 88.50),
(1, '05/12/2023', -88.50),
(1, '05/01/2024', 88.50),
(1, '05/02/2024', 88.50),
(1, '05/03/2024', 88.50),
(1, '04/04/2024', 88.50),
(1, '06/05/2024', 88.50),
(1, '04/06/2024', 88.50),
(1, '28/06/2024', -88.50),
(1, '28/06/2024', -88.50);
GO

5

This may give you want you need!?:


SELECT m.*, CD.Date, CD.Amount 
FROM dbo.Matter m
OUTER APPLY (
    SELECT TOP (1) *
    FROM (
        SELECT CD.*, 
            ROW_NUMBER() OVER(ORDER BY Date DESC, CD.RecordId DESC) AS row_num,
            SUM(Amount) OVER(ORDER BY Date DESC, CD.RecordId DESC) AS running_total
        FROM dbo.ColDebit CD
        WHERE CD.MatterId = m.RecordId
    ) AS derived
    WHERE running_total > 0
    ORDER BY row_num
) AS CD
/*WHERE m.? <op> <value> AND/OR...*/
/*ORDER BY ...*/
2 Likes
6

Hi Scott,

Thanks for the help.

Your script is the best that I have found and it is working.

The only issue that could confuse your script is if there were receipts or reverse receipts for different amounts for the same matter.

I am happy that this would work for 99.9% of the time.

It is also very fast. Perhaps changing it to an inner join would make it faster?

Thanks again.

Eugene

7

You could use CROSS APPLY rather than OUTER APPLY, which is the equivalent of making it an INNER JOIN. I wasn't sure which was correct, so I coded it more widely.

8

I just came here to say: as prevalent as Top N Per Group problems are, it's weird that people often reject a correlated subquery solution for them because they're "a performance problem" or "too complicated". Haters gonna hate. The rest of us are out here deploying efficient solutions.

1 Like
9

Hi SqlHippo,

Are you saying that there is a better solution?

Scott always helps beginners like me when I have issues.

If you can help, I would appreciate another solution.

Thanks.

Eugene.

10

@SqlHippo:

Hmm, an APPLY is a form of correlated subquery. I've found SQL Server and other dbms's to be remarkably efficient at rendering OVER() clauses.

But, as OP said, if you've got a better solution, I'd love to see it.

11

No, sorry for the misunderstanding.

I was saying I like the solution you're providing. I see too many people online rejecting correlated subqueries as valid or performant solutions, often because they either don't understand how to support them with indexes, or they aren't on a database engine like SQL Server that have optimizations for them when there is no usable index available.

1 Like
12

Hi @SqlHippo,

The solution is quick and it works well.

Thanks
Eugene