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: 95405 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: 1000539 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)
Intro to Tally Tables and Functions