March 16, 2015 at 9:22 am
Hey Guys,
I have somewhat issue trying to update column “Amount” based on the percentage of the column “Value”. Existing amount value also must be reduced. Rows linked by RecID and “MyDate” columns
IF (SELECT OBJECT_ID('tempdb..#TestTable1'))is not null
DROP TABLE #TestTable1
SELECT PK, MyDate, RecID, Value, Amount
INTO #TestTable1
FROM (
SELECT 1, '2014-12-03 00:00:00.000','1', 300, 10 UNION ALL
SELECT 2, '2014-12-03 00:00:00.000','1', 200, NULL UNION ALL
SELECT 3, '2014-12-03 00:00:00.000','1', 100, NULL UNION ALL
SELECT 4, '2014-01-13 00:00:00.000','2', 90 , 3 UNION ALL
SELECT 5, '2014-01-13 00:00:00.000','2', 30 , NULL UNION ALL
SELECT 6, '2014-12-03 00:00:00.000','3', 50 , 1
) d (PK, MyDate, RecID, Value, Amount);
SELECT * FROM #TestTable1
So what I expecting to see in the column “Value” for PK 1,2,3 is:
Value PK1 = 5
Value PK2 = 3.3
Value PK3 = 1.7
And so on…
Thank you
March 16, 2015 at 10:54 am
I'm sorry; I'm half-asleep at the moment, so that may be why, but I'm having a hard time understanding the math in your example. The Values listed for PK1, for example, are 300, 200, and 100, and the Amount is 10... how are you getting 5 out of that?
[Edit] Just to clarify, the reason I'm asking is that in order to tell you how to either set up a derived column, or set up your query (depending on what your math is), we do need to know the formula as it exists in your head, that's leading you to the numbers you want to see in your example. 🙂
March 16, 2015 at 10:58 am
Amount PK1 is reduced based on percentage of all recs where Value and date is the same.300 + 200 + 100 = 100%. 300 represents is 50 % of 10 so Amount will be equal 5
March 16, 2015 at 11:19 am
Oh. Ok, I think I see now. 🙂
There are a couple ways I know of, both of which involve queries, not calculated columns in the table.
You could use a correlated subquery, but from what I've heard (I'm also kind of a newbie), those are a bit inefficient.
The other way I can think of goes something like this:
WITH tot(RecID, TotVal, MaxAmt) AS (
SELECT RecID, SUM([Value]), MAX(Amount)
FROM #TestTable1
GROUP BY RecID
)
SELECT v.PK, v.RecID, v.Value, v.Amount, CAST(COALESCE(t.MaxAmt,0) AS Float)*(CAST(v.Value AS Float)/CAST(t.TotVal AS Float))
FROM #TestTable1 v INNER JOIN tot t ON v.RecID = t.RecID
WHERE t.TotVal > 0
This returns:
11300105
21200NULL3.33333333333333
31100NULL1.66666666666667
429032.25
5230NULL0.75
635011
WITH ... AS (SELECT ...), according to my understanding (others may correct me), allows you to generate a subquery (which I called tot) on the fly that only has to be run once (as opposed to correlated subqueries, which run for every row). The subquery grabs the total value for each RecID, and the maximum value stored in the "Amount" column for each RecID (since, for some reason, you're sometimes storing NULLs in there).
Then the outer query runs an equation based on the values from both #TestTable and the subquery, which are linked together by the RecID column.
March 16, 2015 at 11:31 am
It's perfect. Thank you for your help
March 16, 2015 at 6:16 pm
Do you really want those fractional amounts to not add back to the totals?
That can be fixed you know.
Financial Rounding of Allocations [/url]
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply