November 10, 2010 at 7:32 pm
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #myTable(
num INT,
dt DATETIME,
pAve DECIMAL(26,15),
CustIndex DECIMAL(26,15)
)
DECLARE @IndexStart DECIMAL(26,15)
SET @IndexStart = 100
INSERT INTO #mytable (num, dt, pAve)
SELECT 1, 'Oct 17 2007 12:00AM', NULL UNION ALL
SELECT 2, 'Oct 18 2007 12:00AM', -0.007063508628647 UNION ALL
SELECT 3, 'Oct 19 2007 12:00AM', 0.001071299995456 UNION ALL
SELECT 4, 'Oct 20 2007 12:00AM', -0.001088661406197 UNION ALL
SELECT 5, 'Oct 21 2007 12:00AM', -0.002865849842162 UNION ALL
SELECT 6, 'Oct 22 2007 12:00AM', -0.004201103402992 UNION ALL
SELECT 7, 'Oct 23 2007 12:00AM', 0.005926529610740 UNION ALL
SELECT 8, 'Oct 24 2007 12:00AM', -0.003893919494943 UNION ALL
SELECT 9, 'Oct 25 2007 12:00AM', -0.009526173203149
select * from #mytable
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
I am after the formula as per excel file attached..yellow area for the CustIndex field...
Row 1: 100
row 2: (100 * -0.007063508628647) +100 = 99.2936
row 3: (99.2936 * 0.0010713) + 99.2936 = 99.40002242
etc
Any ideas...
November 10, 2010 at 8:11 pm
Please read this article[/url] for how to perform running totals.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 10, 2010 at 8:21 pm
This is more that a running total, its an index calculation, and I have no idea what all article means..
November 10, 2010 at 9:41 pm
Digs (11/10/2010)
This is more that a running total, its an index calculation, and I have no idea what all article means..
Unfortunately Digs, the running totals bit is not a simple thing to build. It requires a lot of knowledge about the underlying DDL, grouping methods available, and a few other things. It's not something that can be implemented easily. What you're looking for with your request is a variation on that.
You've got three things going on here.
1) I'm assuming the DDL above is abbreviated from the actual result. Primarily assuming that from the last question. Also, there's no way to 'group' data. Is this table just a one day entry at all times, or do days repeat for a 'group' of some kind? The reason I ask is we need to know the actual clustered index and full DDL of the table or this can go all sorts of wrong.
2) You want to index on a calculation. Due to the nature of the multi-row calculation, it won't be deterministic. For more information, see here: http://msdn.microsoft.com/en-us/library/ms189292.aspx
3) That requires you to pump this data to a warehousing table that you'll update at certain times. Once you've done that, you'd be able to include the computed column (which is now stored as a hard value) in an index.
EDIT: Triangle join I created doesn't work, doesn't deal with the row to row multiplication. Deciphering different triangle join method.
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
November 10, 2010 at 9:54 pm
Thanks..:-) 🙂 🙂
I need to run this over about 20,000 records once per day.
I assume this wont blow up. 😉
Nice stuff...
November 10, 2010 at 9:58 pm
Digs (11/10/2010)
Thanks..:-) 🙂 🙂I need to run this over about 20,000 records once per day.
I assume this wont blow up. 😉
Nice stuff...
It'll probably blow up, depending on your machine. I edited my above post. That method won't work. It doesn't deal with the multiplication against the previous row's result. This is almost a perfect example of when the quirky/serial update needs to come into play. The problem is that the method is complex.
I'm not entirely sure I can figure out a way to deal with it. I'm messing with a series of log functions at the moment to decipher if it can be done, optimization be damned.
Edit: nogo on the log10, forgot it doesn't play nicely with negatives, and workarounds are giving me very different results.
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
November 11, 2010 at 12:08 am
Here is an attempt at solving your problem using the Quirky Update method. You need to be aware of the rules associated with using this technique as described in Jeff Moden's article:
http://www.sqlservercentral.com/articles/T-SQL/68467/
Since I understand that you are only planning to run this once a day, an optimized cursor solution may well be a acceptable alternative solution for you, if you are uncomfortable using this quirky update technique.
CREATE TABLE #myTable(
num INT NOT NULL PRIMARY KEY CLUSTERED, /* NB: added clustered primary key */
dt DATETIME,
pAve DECIMAL(26,15),
CustIndex DECIMAL(26,15)
)
INSERT INTO #myTable (num, dt, pAve)
SELECT 1, 'Oct 17 2007 12:00AM', NULL UNION ALL
SELECT 2, 'Oct 18 2007 12:00AM', -0.007063508628647 UNION ALL
SELECT 3, 'Oct 19 2007 12:00AM', 0.001071299995456 UNION ALL
SELECT 4, 'Oct 20 2007 12:00AM', -0.001088661406197 UNION ALL
SELECT 5, 'Oct 21 2007 12:00AM', -0.002865849842162 UNION ALL
SELECT 6, 'Oct 22 2007 12:00AM', -0.004201103402992 UNION ALL
SELECT 7, 'Oct 23 2007 12:00AM', 0.005926529610740 UNION ALL
SELECT 8, 'Oct 24 2007 12:00AM', -0.003893919494943 UNION ALL
SELECT 9, 'Oct 25 2007 12:00AM', -0.009526173203149
SET NOCOUNT ON
/* Uses variant of Quirky Update with safety check on num column.
This assumes that there is a clustered index on the num column
and the num column is an incrementing integer in clustered index
order with no gaps. If this is not the case, it would be possible
to roll your own sequential integer column using a CTE and ROW_NUMBER.
*/
DECLARE @PrevNum INT
DECLARE @PrevCustIndex DECIMAL(26,15)
UPDATE #myTable SET
@PrevCustIndex = CustIndex = CASE
WHEN (@PrevCustIndex IS NULL) THEN 100.0
ELSE @PrevCustIndex * (1.0 + pAve) END,
@PrevNum = CASE
WHEN (num = COALESCE(@PrevNum + 1, num)) THEN num
ELSE 1/0 END /* safety check: errors if row processing order differs from expected */
FROM #myTable
WITH (TABLOCKX)
OPTION (MAXDOP 1) /* essential to prevent parallelism */
SELECT * FROM #myTable
November 11, 2010 at 12:34 am
Thanks 🙂
November 15, 2010 at 2:19 am
I see andrewd.smith already did a good job handling your case.
As he stated, reading Jeffs great article certainly is a must if you want to use this method or you may end up with false results because of some types of modifications. I'm not going into details. Read it.
I know the related forum thread may also need time to work through, but at least browse it !
If you have more questions about it, feel free to reply on this from.
Jeff and others will be happy to help out and your Q&A will strengthen the value of the article.:smooooth:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
November 15, 2010 at 5:14 am
Not sure what an "Index Calculation" is, unless it's a computed column associated with an index?
Anyway, here's an alternative to the QU (quirky update) method, a recursive CTE. It's not as fast (at best, about 1/7th compared to a well-prepared QU) but probably fast enough providing you create the index, shown in the sample, on whatever column drives the process flow. The index should be UNIQUE and CLUSTERED for ultimate performance. If you miss out the index, the results will still be correct but the query will be a performance hog for anything over a couple of tens of thousands of rows. Note also that this is a SELECT query and generates output, if you wish to persist the results then you'll have to write them back to the source table.
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #myTable(
num INT,
dt DATETIME,
pAve DECIMAL(26,15),
CustIndex DECIMAL(26,15)
)
--DECLARE @IndexStart DECIMAL(26,15)
--SET @IndexStart = 100
INSERT INTO #mytable (num, dt, pAve)
SELECT 1, 'Oct 17 2007 12:00AM', NULL UNION ALL
SELECT 2, 'Oct 18 2007 12:00AM', -0.007063508628647 UNION ALL
SELECT 3, 'Oct 19 2007 12:00AM', 0.001071299995456 UNION ALL
SELECT 4, 'Oct 20 2007 12:00AM', -0.001088661406197 UNION ALL
SELECT 5, 'Oct 21 2007 12:00AM', -0.002865849842162 UNION ALL
SELECT 6, 'Oct 22 2007 12:00AM', -0.004201103402992 UNION ALL
SELECT 7, 'Oct 23 2007 12:00AM', 0.005926529610740 UNION ALL
SELECT 8, 'Oct 24 2007 12:00AM', -0.003893919494943 UNION ALL
SELECT 9, 'Oct 25 2007 12:00AM', -0.009526173203149
CREATE UNIQUE CLUSTERED INDEX CInum ON #mytable ([num] ASC)
;WITH Calculator AS(
SELECT num, dt, pAve, CustIndex = CAST(100 AS DECIMAL(26,15))
FROM #mytable
WHERE num = 1
UNION ALL
SELECT t.num, t.dt, t.pAve, CAST((c.CustIndex * t.pAve) + c.CustIndex AS DECIMAL(26,15))
FROM #mytable t
INNER JOIN Calculator c ON c.num + 1 = t.num
) SELECT num, dt, pAve, CustIndex
FROM Calculator
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply