Post reply

get Data previous on the same row for 4 previous columns

  • December 4, 2010 at 8:07 pm

    #388367

    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;

    I am try to get this: http://screencast.com/t/8dYSknzZNH

    The current row, also has on it, the 4 immediate previous values...

    Hows that done..

    NOTE: This is not a concatenation, I need the previous values in a dataset field.

    Any Ideas ???

  • December 5, 2010 at 3:53 am

    #1258811

    Something like this?

    ;WITH cte AS

    (

    SELECT ROW_NUMBER() OVER(ORDER BY quote_date ) AS ROW, *

    FROM #priceData

    )

    SELECT

    cte1.*,

    cte2.close_price AS Previous1,

    cte3.close_price AS Previous2,

    cte4.close_price AS Previous3,

    cte5.close_price AS Previous4

    FROM

    cte cte1

    LEFT JOIN cte cte2 ON cte1.row=cte2.row+1

    LEFT JOIN cte cte3 ON cte1.row=cte3.row+2

    LEFT JOIN cte cte4 ON cte1.row=cte4.row+3

    LEFT JOIN cte cte5 ON cte1.row=cte5.row+4

    I used ROW_NUMBER to have a reference to previous rows.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 5, 2010 at 10:12 am

    #1258819

    Nice, Thanks!:-)

  • December 5, 2010 at 10:27 am

    #1258820

    Digs (12/5/2010)

    Nice, Thanks!:-)

    Glad I could help 😀

    If the table is rather large you might want to consider to store the result of the cte in a separate table and index that table on the row column. Otherwise it will be sorted 5 times (see the execution plan for details).


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • December 5, 2010 at 11:10 am

    #1258823

    Thanks again , just save me a 40 minute run !:-)

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

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