Quirky Update TSQL skill required on this calc

Question

Post reply

Quirky Update TSQL skill required on this calc

Digs

SSCertifiable

Points: 7333
More actions
December 10, 2010 at 9:32 pm

#232016

Some data for you
IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;
create table #priceData(quote_date [datetime],close_price [decimal](6,2))
INSERT INTO #priceData (quote_date, close_price) VALUES ('20091026', 555.75)
INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091027', 550.97)
INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091028', 547.87)
INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091029', 543.01)
INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091030', 550.00)
INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091102', 537.08)
CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)
SELECT CONVERT(CHAR(10), quote_date, 101) AS 'Date' ,
close_price AS 'Price'
FROM #priceData
IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;
Ok I am try to crack this calculation...
SET @StartValue = 555.76
20091026, 555.76, 0
20091027, 550.97, 0 + (((550.97-555.76)/@StartValue)*100) = -0.86188
20091028, 547.87, -0.86188 + (((547.87-550.97)/@StartValue)*100) = -1.41968
20091029, 543.01, -1.41968 + (((543.01-547.87)/@StartValue)*100) = -2.29416
etc
formula: PrevCalc + (((CurrPrice - PrevPrice)/@StartValue)*100)
How this done , thanks 🙂

Viewing 4 posts - 1 through 3 (of 3 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

This article walks you through it[/url]. Post back if you have any questions.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

ix_goog? Sounds familiar - take a peek at this[/url].

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

Thanks for the readily-consumable data and the great description of the problem.

First, I added a necessary column to the Temp table during its creation...

IF OBJECT_ID('tempdb..#priceData') IS NOT NULL DROP TABLE #priceData;

create table #priceData(quote_date [datetime],close_price [decimal](6,2), RunningCalc DECIMAL(9,5))

INSERT INTO #priceData (quote_date, close_price) VALUES ('20091026', 555.75)

INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091027', 550.97)

INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091028', 547.87)

INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091029', 543.01)

INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091030', 550.00)

INSERT INTO #priceData (quote_date, close_price ) VALUES ('20091102', 537.08)

CREATE CLUSTERED INDEX ix_goog on #priceData(quote_date)

Then we solve the problem using a "Verified Quirky Update" (verified = "has safety check"). As usual, the details are in the comments. Let me know if you have any other questions after you read them.

--===== Declare the necessary "data smear" variables for the "Verified Quirky Update"

DECLARE @PrevCalc DECIMAL(9,5),

@PrevPrice DECIMAL(9,5),

@SafetyCheck INT,

@StartValue DECIMAL(6,2)

;

--===== Prime the safety check variable

SELECT @SafetyCheck = 1

;

--===== Solve the problem using a "Verified Quirky Update"

WITH

cteSort AS

( --=== Updateable CTE with a row number in the desired order of update.

-- That row number is compared to a simple counter to ensure that the

-- update is being done in the correct order or it will throw an error

-- which, of course, will rollback the entire transaction.

SELECT SafetyCheck = CAST(ROW_NUMBER() OVER (ORDER BY quote_date) AS INT),

close_price,

RunningCalc

FROM #priceData WITH(TABLOCKX) --Ok to leave out on temp tables but an absolute MUST on real tables

)

UPDATE cte

SET @StartValue = CASE --= We could have preset this but this prevents an additional trip to the table.

WHEN @SafetyCheck > 1 --If we're NOT on the first row...

THEN @StartValue --keep the same starting value.

ELSE close_price --Otherwise, set the starting value

END,

@PrevCalc = RunningCalc --Updates the column with the quirky update calculation and remembers it for the next row.

= CASE --= If the safety check passes, then do the quirky update calculation.

--= Otherwise, force an error which will cause a rollback

WHEN SafetyCheck = @SafetyCheck --If the safety counter matches the row number in the updateable CTE...

THEN CASE --then do the quirky update calculation

WHEN @SafetyCheck > 1

THEN @PrevCalc + (((close_price - @PrevPrice)/@StartValue)*100)

ELSE 0

END

ELSE 1/0 --Otherwise, force error if the SafetyCheck counter doesn't match the sorted row number.

END,

@PrevPrice = close_price, --Remember the current price as the previous price for the next row

@SafetyCheck = @SafetyCheck + 1 --Add one to the safety check variable for comparision when we process the next row

FROM cteSort cte

OPTION (MAXDOP 1) --Don't even think of leaving this option out. Parallelism destroys the process.

;

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