Post reply

Inserting rows into the pivoted and updating the date intervals and values based on input.

  • June 2, 2016 at 11:29 pm

    #308159

    My resultant table holds data as:

    Id A B C ValidFrom ValidTo

    1 2 3 5 1900-01-01 9998-12-31

    Here the column A ,B,C represents attributes whose value are 2,3,5 resp in the interval 1900-01-01 to 9998-12-31

    Now someone has changed Attribute value for B in range 1900-01-01 to 2050-12-31 to 5.

    So from the input I will get

    Id Attributeid Value Validfrom validto

    1 B 5 1900-01-01 2050-12-31

    1 B 3 2051-01-01 9998-12-31

    This is the set I will get and now I need to update resultant table on the basis of this and the rows will be breaked into 2.

    Id A B C ValidFrom ValidTo

    1 2 5 5 1900-01-01 2050-12-31

    1 2 3 5 2051-01-01 9998-12-31

    The other attributes value will be copied because their value is same in the new range. And new range is defined in the resultant table because Attribute B value is changed b/w two intervals.

    There will not be overlapping of intevals in the input set.I'll just get the attribute with the values in intervals.

    There can also be the case when the intervals is already defined in the resultant table and I just need to update value.

    for eg if after this I again get new set as

    Id Attributeid Value Validfrom valid to

    1 A 10 1900-01-01 9998-21-31

    the I should having my result as

    Id A B C ValidFrom ValidTo

    1 10 5 5 1900-01-01 2050-12-31

    1 10 3 5 2051-01-01 9998-12-31

    pls help.

  • June 3, 2016 at 3:20 am

    #1883016

    Duplicate post, the original is here.

    Duplicate posts waste time and effort. The original post has useful questions and replies. Responding to this thread will lead to a fragmented domain.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

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