Post reply

T-SQL help needed

  • October 13, 2010 at 8:20 am

    #229633

    declare @Prices TABLE (

    Id int NOT NULL,

    Date smalldatetime NOT NULL,

    CreatedDate smalldatetime NOT NULL,

    Price float NOT NULL,

    Currency int NULL,

    LastChangeDate smalldatetime NULL

    )

    insert @Prices

    select 1,'20030101','20090203','9.32',6,'20030101' union all

    select 1,'20030102','20090203','9.48',6,'20030102' union all

    select 1,'20030103','20090203','9.48',6,null union all

    select 1,'20030106','20090203','9.52',6,'20030106' union all

    select 1,'20030107','20090203','9.48',6,'20030107' union all

    select 1,'20030108','20090203','9.48',6, null

    I need to update table where we have first null value on LastChangeDate=20030102

    and second null value on LastChangeDate=20030107(Logic is : For each asset and date the last change date is the last date on which the price changed before the current date)

    We have similarly very large table and more then 100'000 unique ids.

    Thanks

    Nick

  • October 13, 2010 at 10:12 am

    #1236343

    Does this do what you're looking for?

    UPDATE t1

    SET LastChangeDate = t2.LastChangeDate

    FROM @Prices t1

    JOIN (select Id, [Date], CreatedDate, Price, Currency, LastChangeDate = MAX(LastChangeDate)

    FROM @Prices

    WHERE LastChangeDate IS NOT NULL

    GROUP BY Id, [Date], CreatedDate, Price, Currency) t2

    ON t1.Id = t2.Id

    AND t1.CreatedDate = t2.CreatedDate

    AND t1.Price = t2.Price

    AND t1.Currency = t2.Currency

    WHERE t1.LastChangeDate is NULL

    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

  • October 13, 2010 at 10:40 am

    #1236356

    Thanks for reply.

    Your code updates both NULL values with same max date which is not I am looking.

    First Null value in table should be updated with LastChangeDate=20030102

    ******

    Second Null value in table should be updated with LastChangeDate=20030107

    Thanks

    Nick

  • October 13, 2010 at 11:29 am

    #1236383

    Nick123-481228 (10/13/2010)

    Thanks for reply.

    Your code updates both NULL values with same max date which is not I am looking.

    First Null value in table should be updated with LastChangeDate=20030102

    ******

    Second Null value in table should be updated with LastChangeDate=20030107

    Thanks

    Nick

    Sorry about that.

    How's this:

    WITH CTE AS

    (

    SELECT *,

    RN = ROW_NUMBER() OVER (PARTITION BY Id ORDER BY Date)

    FROM @Prices

    )

    UPDATE CTE

    SET LastChangeDate = CTE2.LastChangeDate

    FROM CTE

    JOIN CTE CTE2

    ON CTE.Id = CTE2.Id

    AND CTE.RN = CTE2.RN+1

    WHERE CTE.LastChangeDate IS NULL;

    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

  • October 13, 2010 at 2:28 pm

    #1236508

    declare @Prices TABLE (

    Id int NOT NULL,

    Date smalldatetime NOT NULL,

    CreatedDate smalldatetime NOT NULL,

    Price float NOT NULL,

    Currency int NULL,

    LastChangeDate smalldatetime NULL

    )

    insert @Prices

    select 1,'20030101','20090203','9.48',6,'20030101' union all

    select 1,'20030102','20090203','9.48',6,'20030102' union all

    select 1,'20030103','20090203','9.48',6,null union all

    select 1,'20030106','20090203','9.52',6,'20030106' union all

    select 1,'20030107','20090203','9.48',6,'20030107' union all

    select 1,'20030108','20090203','9.48',6, null

    In this case your query will fail to min date from where price hasn't been changed

    Thanks

    Nick

  • October 13, 2010 at 3:38 pm

    #1236535

    Your specification is now changed. In http://www.sqlservercentral.com/Forums/FindPost1003800.aspx, you say it should be one value, now you're saying a different thing. Please give detailed specification of what you need.

    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

  • October 13, 2010 at 3:54 pm

    #1236543

    Sorry about that,

    This is equity price table and it contains many combination and it would be very hard to give all cases.

    You can imagine that we are storing 100,00 equity prices on daily basis and we need update the lastchangeDate column of each asset which is begning date of stagnent pricedate of with related all rows of assets.

    Logic is:For each asset and date the last change date is the last date on which the price changed before the current date

    Thanks

    Nick

  • October 13, 2010 at 4:26 pm

    #1236562

    Nick123-481228 (10/13/2010)

    Sorry about that,

    This is equity price table and it contains many combination and it would be very hard to give all cases.

    You can imagine that we are storing 100,00 equity prices on daily basis and we need update the lastchangeDate column of each asset which is begning date of stagnent pricedate of with related all rows of assets.

    Logic is:For each asset and date the last change date is the last date on which the price changed before the current date

    Thanks

    Nick

    Well, I'm not going to bounce this back and forth. Have fun with it... you've got code to see what's going on, and how to do it.

    I don't do work like this for free. If you really want me to work on this, I charge $250/hr, with a minimum daily charge of 8 hrs for any portion of a day that I work on this. Payment must be made in advance, via PayPal. Contact me if interested.

    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

  • October 13, 2010 at 7:14 pm

    #1236597

    Nick123-481228 (10/13/2010)

    declare @Prices TABLE (

    Id int NOT NULL,

    Date smalldatetime NOT NULL,

    CreatedDate smalldatetime NOT NULL,

    Price float NOT NULL,

    Currency int NULL,

    LastChangeDate smalldatetime NULL

    )

    insert @Prices

    select 1,'20030101','20090203','9.48',6,'20030101' union all

    select 1,'20030102','20090203','9.48',6,'20030102' union all

    select 1,'20030103','20090203','9.48',6,null union all

    select 1,'20030106','20090203','9.52',6,'20030106' union all

    select 1,'20030107','20090203','9.48',6,'20030107' union all

    select 1,'20030108','20090203','9.48',6, null

    In this case your query will fail to min date from where price hasn't been changed

    Thanks

    Nick

    So what do you want for the null values to be in this case? '20030102' and '20030107' respectively?

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

  • October 13, 2010 at 7:17 pm

    #1236598

    Nick123-481228 (10/13/2010)

    declare @Prices TABLE (

    Id int NOT NULL,

    Date smalldatetime NOT NULL,

    CreatedDate smalldatetime NOT NULL,

    Price float NOT NULL,

    Currency int NULL,

    LastChangeDate smalldatetime NULL

    )

    insert @Prices

    select 1,'20030101','20090203','9.48',6,'20030101' union all

    select 1,'20030102','20090203','9.48',6,'20030102' union all

    select 1,'20030103','20090203','9.48',6,null union all

    select 1,'20030106','20090203','9.52',6,'20030106' union all

    select 1,'20030107','20090203','9.48',6,'20030107' union all

    select 1,'20030108','20090203','9.48',6, null

    In this case your query will fail to min date from where price hasn't been changed

    Thanks

    Nick

    One more question, Nick... what is the Primary Key of this table?

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

  • October 14, 2010 at 2:34 am

    #1236678

    Primary key columns are Id & Date(compsite key)

    Thanks

    Nick

Viewing 11 posts - 1 through 10 (of 10 total)

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