Find all records in a table whose values when summed up will equal another value

Question

Post reply

Find all records in a table whose values when summed up will equal another value

kirkdevilliers

Valued Member

Points: 63
More actions
April 14, 2025 at 7:54 pm
Go to Answer
#4578924

I have a list of transactions in a table. Each transaction has a unique id which is a Guid, a date, a product that was ordered and an amount. I need to find a way that will filter out the transactions for a specific day that when added together will give me a specific value. Looking at the example table below if I wanted to get my target value for the 01 Apr 2025 to be 1000 it would return id 1,2,4,5
Second example if I wanted to return a list of transactions where my target value was 1500 for the 02 Apr 2025 it would return id 6 and 9
declare @tx table
(
Id int identity(1,1),
TransactionId uniqueidentifier,
Date DATE,
ProductCode VARCHAR(6),
Amount DECIMAL(22,6)
)
insert into @tx values (NEWID(),'01 Apr 2025','0190',150)
insert into @tx values (NEWID(),'01 Apr 2025','2200',350)
insert into @tx values (NEWID(),'01 Apr 2025','0190',250)
insert into @tx values (NEWID(),'01 Apr 2025','1300',400)
insert into @tx values (NEWID(),'01 Apr 2025','1500',100)
insert into @tx values (NEWID(),'02 Apr 2025','0191',600)
insert into @tx values (NEWID(),'02 Apr 2025','0192',1200)
insert into @tx values (NEWID(),'02 Apr 2025','0193',600)
insert into @tx values (NEWID(),'02 Apr 2025','0194',900)
T1

Emperor100

SSCrazy

Points: 2205

April 14, 2025 at 11:30 pm

Answer

#4579011

One way is to sum up row by row to see if it matches target amount.

DECLARE @TargetDate DATE = '2025-04-02';
DECLARE @TargetAmount DECIMAL(22,6) = 1800.000000;
;
WITH Combinations AS (
    SELECT 
        Id,
        Amount,
        CAST(Id AS VARCHAR(MAX)) AS IDPath,
        1 AS Depth
    FROM @tx
    WHERE Date = @TargetDate
    UNION ALL
    SELECT 
        t.Id,
        CAST(c.Amount + t.Amount AS DECIMAL(22,6)) AS Amount,  
        c.IDPath + ',' + CAST(t.Id AS VARCHAR(MAX)) AS IDPath,
        c.Depth + 1 AS Depth
    FROM Combinations c
    JOIN @tx t ON 
        t.Date = @TargetDate AND
        t.Id > c.Id AND  
        c.Amount + t.Amount <= @TargetAmount 
    WHERE c.Amount < @TargetAmount
)
SELECT 
    IDPath AS MatchingIDs,
    Amount AS TotalAmount
FROM Combinations
WHERE Amount = @TargetAmount
ORDER BY Depth, IDPath
OPTION (MAXRECURSION 100);

=======================================================================

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101187 More actions · Answer 1

I have a function that does that. It could also return 400 and (either) 600. You'd have to specify how to pick one over the other.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jonathan AC Roberts SSCoach Points: 18406 More actions · Answer 2

kirkdevilliers wrote:

I have a list of transactions in a table. Each transaction has a unique id which is a Guid, a date, a product that was ordered and an amount. I need to find a way that will filter out the transactions for a specific day that when added together will give me a specific value. Looking at the example table below if I wanted to get my target value for the 01 Apr 2025 to be 1000 it would return id 1,2,4,5

Why would it return (1,2,4,5) and not (1,2,3) or (1,3,6) or (1,3,8) or (2,3,4) or (5,9) or (4,6) or (4,8)?

Jonathan AC Roberts SSCoach Points: 18406 More actions · Answer 3

kirkdevilliers wrote:

I have a list of transactions in a table. Each transaction has a unique id which is a Guid, a date, a product that was ordered and an amount. I need to find a way that will filter out the transactions for a specific day that when added together will give me a specific value. Looking at the example table below if I wanted to get my target value for the 01 Apr 2025 to be 1000 it would return id 1,2,4,5

Why would it return (1,2,4,5) and not (1,2,3) or (1,3,6) or (1,3,8) or (2,3,4) or (5,9) or (4,6) or (4,8)?

Ross McMicken SSCarpal Tunnel Points: 4455 More actions · Answer 4

This seems incomplete. What is the basis for the totals? Sales to a given customer(there is no customer column)? Total amount of a single product made or sold? You could have many answers that all add up to the target value. Is that OK, or do you need a deterministic answer?

ScottPletcher SSC Guru Points: 101187 More actions · Answer 5

My function by default returns all matches, but you have options to prioritize fewer rows matching or more rows matching.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kirkdevilliers Valued Member Points: 63 More actions · Answer 6

Thanks to everyone for replying, the response by Emperor100 is exactly what I'm looking for, it works like a charm

Jeff Moden SSC Guru Points: 1004577 More actions · Answer 7

ScottPletcher wrote:

My function by default returns all matches, but you have options to prioritize fewer rows matching or more rows matching.

So post it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)