Just for fun I did a brute force solution. It will find combinations of one, two, or three records that add up to Total_amt. You could expand it to handle more transactions per sum, by adding d4, d5 subselects, etc.:
begin tran
create table Table_1 (D_ID int, Deposit_amt int)
create table Table_2 (Total_ID int, Total_amt int)
insert into Table_1 (D_ID, Deposit_amt) values (1, 4)
insert into Table_1 (D_ID, Deposit_amt) values (2, 3)
insert into Table_1 (D_ID, Deposit_amt) values (3, 1)
insert into Table_1 (D_ID, Deposit_amt) values (4, 1)
insert into Table_1 (D_ID, Deposit_amt) values (5, 9)
insert into Table_1 (D_ID, Deposit_amt) values (6, 13)
insert into Table_1 (D_ID, Deposit_amt) values (7, 6)
insert into Table_1 (D_ID, Deposit_amt) values (8, 7)
insert into Table_1 (D_ID, Deposit_amt) values (9, 12)
insert into Table_1 (D_ID, Deposit_amt) values (10, 4)
insert into Table_2 (Total_ID, Total_amt) values (1, 17)
insert into Table_2 (Total_ID, Total_amt) values (2, 23)
insert into Table_2 (Total_ID, Total_amt) values (3, 55)
insert into Table_2 (Total_ID, Total_amt) values (4, 4)
select t.Total_amt,
d1.D_ID as d1_ID, d1.Deposit_amt as d1_amt,
d2.D_ID as d2_ID, d2.Deposit_amt as d2_amt,
d3.D_ID as d3_ID, d3.Deposit_amt as d3_amt
from Table_2 t
cross join (
select D_ID, Deposit_amt from Table_1
) d1
inner join (
select D_ID, Deposit_amt from Table_1
union all
select null, null
) d2 on d1.D_ID > d2.D_ID or d2.D_ID is null
inner join (
select D_ID, Deposit_amt from Table_1
union all
select null, null
) d3 on d2.D_ID > d3.D_ID or d3.D_ID is null
where isnull(d1.Deposit_amt, 0) + isnull(d2.Deposit_amt, 0) + isnull(d3.Deposit_amt, 0) = t.Total_amt
order by Total_amt
rollback tran
Output:
Total_amt d1_ID d1_amt d2_ID d2_amt d3_ID d3_amt
----------- ----------- ----------- ----------- ----------- ----------- -----------
4 3 1 2 3 NULL NULL
4 4 1 2 3 NULL NULL
4 1 4 NULL NULL NULL NULL
4 10 4 NULL NULL NULL NULL
17 9 12 3 1 1 4
17 9 12 4 1 1 4
17 10 4 5 9 1 4
17 8 7 7 6 1 4
17 6 13 1 4 NULL NULL
17 10 4 6 13 NULL NULL
17 10 4 8 7 7 6
17 8 7 5 9 4 1
17 10 4 9 12 4 1
17 8 7 5 9 3 1
17 10 4 9 12 3 1
17 6 13 3 1 2 3
17 6 13 4 1 2 3
23 8 7 6 13 2 3
23 6 13 5 9 3 1
23 6 13 5 9 4 1
23 10 4 7 6 6 13
23 10 4 9 12 8 7
23 7 6 6 13 1 4
23 9 12 8 7 1 4
(24 row(s) affected)
Note: You could filter out individual rows whose Deposit_amt > Total_amt, but this would probably not help performance much unless Deposit_amt was indexed.
