September 17, 2010 at 7:06 pm
Howdy folks.
I'm trying to figure out a way to rowset multiply. I can sum, avg, checksum, and I've even done some running totals, but I'm running into a problem determining a multi-row multiplier.
The short story: I determined a neat little algorithm to deal with point in time share values, but I'm getting stuck on rowset multiplication. Hopefully someone's got some ideas.
Test Structure:
CREATE TABLE #StockSplits
(SecurityIDINT,
SplitDateDATETIME,
ShareInINT,
ShareOutINT,
MultiplierNUMERIC( 18, 6)
)
INSERT INTO #StockSplits
SELECT 144, '08/01/2006', 1, 1.01988, 1.01990000000000000000 UNION ALL
SELECT 144, '08/06/2007', 1, 1.02, 1.02000000000000000000 UNION ALL
SELECT 144, '07/25/2008', 1, 1.03, 1.03000000000000000000 UNION ALL
SELECT 144, '07/28/2009', 1, 1.01, 1.01000000000000000000 UNION ALL
SELECT 27182, '05/27/2004', 20, 21, 1.05000000000000000000 UNION ALL
SELECT 27182, '08/01/2006', 1, 1.03, 1.03000000000000000000 UNION ALL
SELECT 27182, '08/10/2007', 1, 1.01991, 1.01990000000000000000 UNION ALL
SELECT 27182, '07/31/2008', 20, 21, 1.05000000000000000000 UNION ALL
SELECT 27182, '08/12/2009', 1, 1.03, 1.03000000000000000000
Sample Query: (This doesn't work because I'm not sure HOW to get the multiplier to work).
DECLARE @BeginDate DATETIME,
@ReportFor DATETIME
SELECT@BeginDate = '1/1/2007',
@ReportFor = '1/1/2009'
SELECT
ss.SecurityID,
MULTIPLY ( ss.Multiplier) AS TotMult
FROM
#StockSplits AS ss
WHERE
ss.SplitDate between @BeginDate and @ReportFor
GROUP BY
ss.SecurityID
What I'd like is for it to return two rows (not including the comment part, just there for reference):
securityIDTotMult(Comment)
1441.0506001.02 * 1.03
271821.0708951.0199 * 1.05
Now, I've a BAD feeling I'm going to have to do some fancy footwork to twist the row up via a pivot, and then do a multi-column computation to determine this value. I'm attempting to apply the result of this little gambit (@BeginDate) against the actual purchase date of the stock, so that's actually a moving parameter based on per row of a transaction table.
I've messed with the pivot a little, but I haven't done heavy work in it before. Does that sound like a reasonable solution? Am I missing one? I'm not sure I've recognized any other reasonable approach.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 17, 2010 at 7:55 pm
Well it's not pretty, but you can get the LOG10() of the numbers, sum them and then raise 10 to that power.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 17, 2010 at 8:25 pm
drew.allen (9/17/2010)
Well it's not pretty, but you can get the LOG10() of the numbers, sum them and then raise 10 to that power.Drew
That's a deliciously beautiful workaround to the ideas I had in mind. I'll give 'er a go. Thank you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 17, 2010 at 11:50 pm
That's just beautiful:
SELECT
ss.SecurityID,
POWER( 10.000000, SUM( LOG10( multiplier))) AS TotMult
FROM
#StockSplits AS ss
WHERE
ss.SplitDate between @BeginDate and @ReportFor
GROUP BY
ss.SecurityID
Works like a charm! Thank you again sir.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply