Appropriate idea or SQL to obtain the results set

Question

Fig 1

TxnId   | TxnTypeId |BranchId   |TxnNumber  |LocalAmount    |ItemName
--------------------------------|-----------|---------------|----------
1777486 | 101       |1099       |1804908    |65.20000000    |A
1777486 | 101       |1099       |1804908    |324.50000000   |B
1777486 | 101       |1099       |1804908    |97.20000000    |C
1777486 | 101       |1099       |1804908    |310.00000000   |D
1777486 | 101       |1099       |1804908    |48.90000000    |E

Fig 2

TxnId   |TxnTypeId  |BankId |Number  |Check  |Bank  |Cash  |Wallet
--------|-----------|-------|--------|-------|------|------|------
1777486 |101        |1099   |1804908 |  48.9 |  310 |389.7 |97.2

Fig 3 (Expected Output)

TxnId   |BankId |ItemName   |Amount |Wallet |Bank   |Check  |Cash
--------|-------|-----------|-------|-------|-------|-------|-------
1777486 |1099   |A          |65.2   |0  0   |0      |0      |65.2
1777486 |1099   |B          |324.5  |0  0   |0      |0      |324.5
1777486 |1099   |C          |97.2   |97.2   |0      |0      |0
1777486 |1099   |D          |48.9   |0      |0      |48.9   |0
1777486 |1099   |E          |310    |0      |310    |0      |0

I have two different result set that is obtained from the different query.

Fig 1 and Fig 2.

The Result i wanted is like shown in fig 3.

Currently i do not have the flag to identify the payment mode use for each transaction(each item). I have the flag for only the complete transaction.

Fig 4

IndividualTxnPaymentDetailId|   IndividualTxnId |PaymentAmount  |PaymentMode
---------------------------:|:-----------------:|:-------------:|:--------------
2106163                     |   1777486         |389.70000000   |   Cash
2106164                     |   1777486         |97.20000000    |   Wallet
2106165                     |   1777486         |310.00000000   |   Bank
2106166                     |   1777486         |48.90000000    |   Check

Means if two item or more is purchased using one payment mode i do not have the proper way of identifying the payment done for each item.

Item A and B is purchased using cash as payment mode with the amount 65.2 and 324.5. Total Cash paid is 389.7

Item C is purchased using Wallet as payment mode with amount 97.2. Total Wallet amount is 97.2.

Fig 5

TxnId   |LocalAmount    |ItemName
--------|--------------:|:------------
1777486 |65.20000000    |   A 
1777486 |324.50000000   |   B
1777486 |97.20000000    |   C
1777486 |310.00000000   |   D
1777486 |48.90000000    |   E

Query by which i generated the result in Fig 4 and Fig 5

select IndividualTxnPaymentDetailId, IndividualTxnId, PaymentAmount, cc.choicecode as PaymentMode
from dbo.IndividualTxnPaymentDetail it
inner join configchoice cc on cc.configchoiceid= it.configpaymentmodeid
where IndividualTxnId = 1777486

select IndividualTxnId as TxnId, LocalAmount, CurrencyName from dbo.IndividualTxnFCYDetail where IndividualTxnId = 1777486

This is the query written to identify the transaction made through Bank. Similarly i wanted to get the transaction on all the payment mode. But could not obtain the transaction properly.

CASE
WHEN tpm.Bank - SUM(txn.LocalAmount) OVER (PARTITION BY txn.BranchId, txn.TxnNumber ORDER BY CAST(txn.ItemName AS varchar(300))) + txn.LocalAmount < 0 THEN 0
WHEN tpm.Bank - SUM(txn.LocalAmount) OVER (PARTITION BY txn.BranchId, txn.TxnNumber ORDER BY CAST(txn.ItemName AS varchar(300))) + txn.LocalAmount > txn.LocalAmount THEN txn.LocalAmount
WHEN tpm.Bank - SUM(txn.LocalAmount) OVER (PARTITION BY txn.BranchId, txn.TxnNumber ORDER BY CAST(txn.ItemName AS varchar(300))) + txn.LocalAmount > tpm.Bank THEN tpm.Bank
ELSE tpm.Bank - SUM(txn.LocalAmount) OVER (PARTITION BY txn.BranchId, txn.TxnNumber ORDER BY CAST(txn.ItemName AS varchar(300))) + txn.LocalAmount
END AS Bank,

Can you help me to get the idea or with some sql to get the result set as in fig 3.

Answer 1

I think part of what you're looking for is buried in here:

http://www.itprotoday.com/software-development/algorithms-still-matter

it calls it 'order fulfillment' where you go through transactions, combining them until you reach a given total

I think the solution will be in multiple parts, including cursors etc.

I'm not convinced you would be able to understand or implement any solution posted. Also, I maintain that there are cases where there are ambiguous solutions.

Lastly I see you have asked 16 questions and not marked a single one as answered.

Answer 2

The Continuing Saga

Comment: [A version of this] report has already been made ...[but] I cannot contact the person who actually wrote that thing.

Perhaps he was also asked to do something that didn't make sense but did it anyway. The math simply doesn't work. He may have written something that finds as many one-to-one matches as it can and then sort of rolls the dice on the rest of it. He may have done something like the following:

• Find and eliminate all the one-to-one matches.
• Take any total and subtract any item amount from it to see if it matches any remaining item amounts(s), if so, arbitrarily pick one, eliminate all three numbers.
• Repeat this until all combinations have been tested.

But you are still potentially left with unmatched numbers, so you next need to test for sums of three numbers by:

• Arbitrarily subtract any two item amounts from any of the remaining totals.
• and so on and so on, followed by testing for sums of four items and so on.

Answer 3

Updated Question - Updated Responce

I read your updated question and I'm afraid the problem still stands. Neither of those queries are summing the data - they are just pulling the same already summed numbers. You would either need to get at the numbers prior to the aggregation happening -or- to have some column in your IndividualTxnPaymentDetail table that ties each row to its counterpart rows in the other table (presumably through a cross table as in - Row 1 : ItemName A, Row 1 : ItemName B, Row 2 : ItemName C, etc).

If these are simply impossible, then perhaps your approaching this the wrong way, or to put it better, perhaps you are being asked to do something that doesn't make sense - and provable so. If there is no direct relationship between these activities in the data there's not much you can be expected to do. What's more it may indicate that your organization doesn't 'think' about them that way.

These two tables seem be payments and liabilities. Perhaps consider an approach where each payment goes toward what ever the oldest outstanding balance is and are matched to the items in Fig 4 that way. Add a column to the details table to store payment toward that item. Rather than a simple Paid/Unpaid Boolean, I would store the amount of payment that has been applied toward each item or the amount still owed on each item; that way you can handle partially applied payments. As payments come in, apply them. You would likely want a similar column in the payments table too to measure the amount of each payment that you have applied; that way you can handle over-payments, and be able to know the status of things such as pending receipts in the case that payments aren't applied immediately.

I hope this helps.

Answer 4

Fundamental Flaw

Your question is looking to take aggregated data (in your example, the Fig 2 Cash total of 389.7) and tease out what numbers were totaled to get the sum. You can do it here since 3 of the 4 numbers in Fig 2 are unique, one-to-one matches with numbers in Fig 1 - meaning the remaining ones have to belong to each other. But imagine 100s of numbers, many or most of them sums (i.e. not one-to-one matches like most of these). Or imagine an example as simple as yours except the numbers aren't so unique (e.g. Fig 1 = (10, 10, 10, 10, 20) and Fig 2 = (10, 20, 20, 10) - it is not possible to say which ones are which) and there only needs to be two possible combinations that could be responsible for a particular sum for the results to become ambiguous.

The weakness is in Fig 2. Do you have any control over that data source? Can grab the numbers up-stream before they are totaled?

Sorry for the negative conclusion but...

I hope this helps.