StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Just for fun I did a brute force solution. It will find combinations of one, two, or three records that add up to <code>Total_amt</code>. You could expand it to handle more transactions per sum, by adding <code>d4</code>, <code>d5</code> subselects, etc.:</p> <pre><code>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 </code></pre> <p><strong>Output:</strong></p> <pre><code>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) </code></pre> <p><strong>Note:</strong> You could filter out individual rows whose <code>Deposit_amt > Total_amt</code>, but this would probably not help performance much unless <code>Deposit_amt</code> was indexed.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload