Extracting Operands From a Final Value

Question

Extracting Operands From a Final Value

cdun2

SSCertifiable

Points: 7893
More actions
August 8, 2007 at 12:29 pm

#197234

Hello
I have a table with two columns. One column is SmallDateTime (TxnDate), and the other is decimal(18,2) (TxnAmount). With this data, I need to be able to find out which TxnAmount values can be added together to arrive at a certain total.
For example, if someone gives me a number like 3680.97, I need to know which TxnAmount records, when summed, will arrive at that total.
Is there any way to do this?
Thank you for your help!
CSDunn

Viewing 6 posts - 1 through 5 (of 5 total)

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

Loner SSC-Insane Points: 21279 More actions · Answer 1

Can you give more info of what you want to do? When you said someone gave you a number and you wanted to know which records would sum up together to get it, it is kind of vague.

Do you want to get the total amount by certain date?

Create table TEMPTABLE (TempID INT,

TxnDate SmallDateTime,

TxnAmount Decimal(18,2))

To get the total sum of TxnAmount based on the date

SELECT TempID, CONVERT(VARCHAR(10), TxnDate, 112) TxnDate, SUM(TxnAmount) TotAmt

INTO #TempDate

FROM TEMPTABLE

GROUP BY TempID, CONVERT(VARCHAR(10), TxnDate, 112)

This query would tell you which date to get the sum of TxnAmount.

SELECT TempID, TxnDate

FROM #TempDate

WHERE TotAmt = 3680.97

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

I have a list of 24 numbers in an Excel worksheet, and I needed to figure out which combination of numbers, added togther, would give a result of 194.50.

Moments ago, I came up with a solution for this:

************************

SELECT * INTO #MixNumbers

FROM

OPENDATASOURCE

( 'Microsoft.Jet.OLEDB.4.0',

'Data Source=C:\LocalFiles\Book5.xls; Extended Properties=Excel 8.0')...Sheet1$

SELECT DISTINCT

t1Spend,

t2Spend,

t3Spend,

t4Spend,

t5Spend,

t1Spend + t2Spend + t3Spend + t4Spend + t5Spend as TotalSpend

FROM

(

SELECT t1.Spend as t1Spend, t2.Spend as t2Spend, t3.Spend as t3Spend,

t4.Spend as t4Spend, t5.Spend as t5Spend

FROM #MixNumbers t1, #MixNumbers t2, #MixNumbers t3, #MixNumbers t4, #MixNumbers t5

) AS th

WHERE t1Spend + t2Spend + t3Spend + t4Spend + t5Spend = 194.5

ORDER BY t1Spend + t2Spend + t3Spend + t4Spend + t5Spend

************************

Basically, I just cross joined the numbers until the 194.5 result appeared.

rubes SSCrazy Points: 2058 More actions · Answer 3

rubes

SSCrazy

Points: 2058

August 9, 2007 at 10:34 am

#725805

That's pretty cool.

Regards,
Rubes

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 4

Nicely done... but, what if it takes 6 numbers to make the desired total?

--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)

rubes SSCrazy Points: 2058 More actions · Answer 5

Good point, I spoke too soon. Let me qualify my comment. Neat trick if you need to do some data mining in a non-production situation.... Like you missed an AND statement in your WHERE clause and updated too many rows... and now you've got to figure out which rows you've updated before your boss realizes it... and before credit cards get processed at 4 PM....

Regards,
Rubes