Post reply

Update previous close price.

  • February 23, 2009 at 7:22 am

    #72435

    I have a inventory table which contains data of around 3000 inventory and their price for each day.The table contains data as open_price,close_price,date,prev_close.The Prev_close column should contains data of the close_price of previous day for each inventory.The text file from which I import data into the table contains

    all data except prev_close. I have to write a query to update the Prev_close column for each inventory.Since the data present is of two years I have a table with around 1.5 million data.Is it possible to write a single query to update the prev_close price. I have done a query but it is not updating the required data to prev_close column.

    Please help.The temp table is attached in text file.

  • February 23, 2009 at 7:42 am

    #948589

    If you are really in SQL 2000 this link won't help, but if you are in 2005 this article may help you:

    http://www.sqlservercentral.com/articles/T-SQL/62159/

    Are your dates always consecutive or can there be gaps? In your test data there are not gaps and the solution for SQL Server 2000 would be different if there are gaps.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 23, 2009 at 7:55 am

    #948603

    Yes, it is possible but not easily in a single query especially since you probably have the clustered index on the IDENTITY column (good idea for this type of table). What is the clustered index on this table?

    Also, do you allow Temp tables to be used on your system... this get's real easy and real fast if you do.

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

  • February 23, 2009 at 8:05 am

    #948612

    This is not perfect and I am pretty sure Jeff will have a better solution, but here's an idea:

    UPDATE dbo.Temp

    SET Prev_close = (SELECT PREV.Close_Price

    FROM

    dbo.Temp AS CURR JOIN

    dbo.Temp AS PREV

    ON CURR.Inventory_Code = PREV.Inventory_Code AND

    CURR.Detail_Date =

    DATEADD(DAY, CASE

    WHEN DATENAME(dw, CURR.Detail_Date) = 'MOnday' THEN 3

    ELSE 1

    End,

    PREV.Detail_Date)

    WHERE

    dbo.Temp.Detail_Id = CURR.Detail_Id)

    The case handles gaps caused by weekends by not any other gaps.

    Edit: Eliminate horizontal scroll at least on my screen.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 23, 2009 at 8:11 am

    #948617

    I haven't tested it, but Jack normally writes code that works especially since you posted some test data. My concern is that of performance... this does have to run against 1.5 million rows.

    It is possible to do this update on 1.5 million rows in a very short amount of time... but I need answers to my previous questions before I can proceed.

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

  • February 23, 2009 at 10:10 pm

    #949140

    As Jeff Moden replied I can even use temp tables if it is not possible with a single query.But it would help if we were to do it in a single query.The dates can have gaps if there is a general holiday then there will be no data for that day but for a given date there will be a unique Inventory and no duplicate of any inventory.

  • February 24, 2009 at 6:18 am

    #949281

    I take a swing at it after work tonight.

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

  • February 24, 2009 at 6:18 am

    #949282

    Ah... almost forgot... you still haven't told me what the clustered index on the table is.

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

  • February 24, 2009 at 6:46 am

    #949296

    The clustered index on the table is for Detail_id. I have created a procedure for this but it is very slow, for a single inventory update it takes about 8 min(a single inventory has around 500 enteries i.e 500 different dates).How can I make this procedure faster or is there any other way.

    Create PROCEDURE Stp_UpdatePrevioiusClosePrice @Inventory_Code int = null

    as

    BEGIN

    SET NOCOUNT ON

    DECLARE @id int,@count int,@id1 INT ,@count1 INT,@Detail_Date datetime,@close_price decimal(10,2)

    DECLARE @TABLE Table (ID INT IDENTITY(1,1),Inventory_Code INT)

    DECLARE @table1 table (ID INT IDENTITY(1,1),Detail_Date datetime)

    IF @Inventory_Code IS NULL

    INSERT INTO @table (Inventory_Code) SELECT DISTINCT Inventory_Code FROM Temp order by Inventory_Code

    ELSE

    INSERT INTO @table (Inventory_Code) SELECT @Inventory_Code

    SELECT @count = COUNT(*) FROM @table

    set @id = 1

    WHILE (@ID <= @count)

    BEGIN

    SELECT @Inventory_Code = Inventory_Code FROM @table WHERE ID = @ID

    IF (SELECT Prev_close FROM temp where Inventory_Code = @Inventory_Code and detail_date = (select min(detail_date) FROM temp where Inventory_Code = @Inventory_Code )) IS NULL

    UPDATE temp SET Prev_close = Open_Price where Inventory_Code = @Inventory_Code and detail_date = (select min(detail_date) FROM temp where Inventory_Code = @Inventory_Code)

    INSERT INTO @table1(Detail_Date) Select distinct detail_date from temp where Inventory_Code = @Inventory_Code order by detail_date

    set @count1 = @@Rowcount

    SET @ID1 = 2

    WHILE (@ID1 <= @Count1)

    BEGIN

    SELECT @Detail_Date = Detail_Date FROM @TABLE1 WHERE id = @id1

    select @close_price = close_price from temp where Inventory_Code = @Inventory_Code AND detail_date =(select max(detail_date) from temp where detail_date < @Detail_Date )

    UPDATE temp set Prev_close = @close_price where Inventory_Code = @Inventory_Code AND detail_date = @Detail_Date

    SET @ID1 = @ID1 + 1

    END

    set @id = @id + 1

    END

    SET NOCOUNT OFF

    END

    go

    DROP PROCEDURE Stp_UpdatePrevioiusClosePrice

    GO

  • February 24, 2009 at 8:10 pm

    #949754

    Ashwin M N (2/24/2009)

    The clustered index on the table is for Detail_id. I have created a procedure for this but it is very slow, for a single inventory update it takes about 8 min(a single inventory has around 500 enteries i.e 500 different dates).How can I make this procedure faster or is there any other way.

    Okidoki... just a couple of assumptions that must be true...

    First, you said the clustered index in on the Detail_ID column. From your test data, I'm assuming that Detail_ID preserves the order of Inventory_Code and Detail_Date. If it does not, then this won't work by itself... lemme know...

    If the assumptions above are correct, this will update all 1.5 million rows correctly in 10 seconds or less... no, I'm not kidding you. 10 seconds or less...

    --===== Declare some obviously named variables

    DECLARE @Prev_Close DECIMAL(10,2)

    DECLARE @Prev_Inventory_Code INT

    --===== Do the "data smear" using a "quirky update"

    UPDATE dbo.Temp

    SET @Prev_Close = Prev_Close = CASE WHEN Inventory_Code = @Prev_Inventory_Code

    THEN @Prev_Close

    ELSE Open_Price

    END,

    @Prev_Close = Close_Price,

    @Prev_Inventory_Code = Inventory_Code

    FROM dbo.Temp WITH(INDEX(0),TABLOCKX)

    If the assumptions are not correct, post back with the details... no matter what, this is neither a difficult nor slow problem.

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

  • February 24, 2009 at 8:27 pm

    #949760

    Ah yes, this is one of those tools I need to remember. I don't have to deal with updates like this that often so I always forget about this update method.

    Oh, and Jeff, it's not difficult for you, but you've built your reputation on high performance. Some of us have not been as diligent about performance as you have so we still don't have or remember all the tools available like you do.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 24, 2009 at 8:55 pm

    #949765

    Jack Corbett (2/24/2009)

    Ah yes, this is one of those tools I need to remember. I don't have to deal with updates like this that often so I always forget about this update method.

    Oh, and Jeff, it's not difficult for you, but you've built your reputation on high performance. Some of us have not been as diligent about performance as you have so we still don't have or remember all the tools available like you do.

    Gosh, Jack, I hope you didn't take what I said in a way I didn't mean it. When I said you normally write code that works, I meant it and the code you posted on this thread is no exception. No slam was intended and I sure am sorry if it sounded that way. It was actually meant as a compliment.

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

  • February 24, 2009 at 9:04 pm

    #949769

    Jeff,

    I didn't take any offense at what you said, I took it as a compliment, and my post was supposed to be a compliment as well. Just kind of pointing out that what is easy or common to you is difficult or uncommon to others because we don't have the same experience as you do. It's what makes putting in a solution on a thread you are involved in interesting because I get a chance to learn something new or get reminded of something I've seen before, but never used.

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • February 24, 2009 at 9:12 pm

    #949773

    Thanks Jack... lotta tension on this side, lately, and I had to make sure I wasn't tuggin' on a friend's chain.

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

  • February 25, 2009 at 3:41 am

    #949857

    Thanks for your advice I was able do it, please see the attachment and help me in optimizing the procedure.

Viewing 15 posts - 1 through 15 (of 22 total)

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