Query Help

  • Hi

    I have a requirement to update my enddate with value which is 1 day less than other row start date.

    My table looks like

    Rowiddeccode tariefStartdateEnddate

    101012743002005-01-01NULL

    201012743002006-01-01NULL

    301012743002008-01-01NULL

    401022743002005-01-01NULL

    501022743002006-01-01NULL

    And it should have updated ENDDATE AS

    Rowiddeccode tarief Startdate Enddate

    101012743002005-01-012005-12-31

    201012743002006-01-012007-12-31

    301012743002008-01-012004-12-31

    401022743002005-01-012005-12-31

    501022743002006-01-012099-01-01

  • How do you know which row the enddate should be derived from?

    Are there gaps in the sequence suggested by column RowID?

    “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

  • There are no gaps in rowid.

    The enddate for row = 1 should come from rowid = 2,.... and for last rowid there is default value

  • If RowID has no gaps and can be reliably used to identify "the other row", then something like this should work for you:

    CREATE TABLE #MyTable (

    Rowid INT,

    deccode CHAR(6),

    tarief INT,

    Startdate DATETIME,

    Enddate DATETIME)

    INSERT INTO #MyTable (Rowid, deccode, tarief, Startdate, Enddate)

    SELECT 1, '010127', 4300, '2005-01-01', NULL UNION ALL

    SELECT 2, '010127', 4300, '2006-01-01', NULL UNION ALL

    SELECT 3, '010127', 4300, '2008-01-01', NULL UNION ALL

    SELECT 4, '010227', 4300, '2005-01-01', NULL UNION ALL

    SELECT 5, '010227', 4300, '2006-01-01', NULL

    SELECT s.Rowid, s.deccode, s.tarief, s.Startdate,

    e1.Startdate,

    e2.Startdate,

    AnEndDate = DATEADD(d,-1, s.Startdate)

    FROM #MyTable s

    -- Guessing there's a partition on deccode

    LEFT JOIN #MyTable e1 ON e1.deccode = s.deccode

    AND e1.Rowid = s.Rowid+1

    LEFT JOIN #MyTable e2 ON e2.deccode = s.deccode

    AND e2.Rowid = s.Rowid-1

    “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

  • The query does not give result as desired.

    The enddate for rowid = 1 should be 2005-12-31 while it gives 2004-12-31.

    The query seems to be substracting 1 from the same rowid's startdate and not the next row's start date.

  • khushbu (8/12/2010)


    The query does not give result as desired.

    The enddate for rowid = 1 should be 2005-12-31 while it gives 2004-12-31.

    The query seems to be substracting 1 from the same rowid's startdate and not the next row's start date.

    Of course.

    At the time the query was written, your requirements were unclear. The column "AnEndDate" merely shows you how to subtract one day from a datetime.

    The column you want to use for your enddate is either e1.Startdate or e2.Startdate, with one day subtracted.

    “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

  • e1.startdate or e2.startdate can be used, but dont know why, there is NULL in some rows, while there exists data in all the rows of s.startdate.

    I know NULL can come in last row if e1.startdate and last 2 rows of e2.startdate but in between NUll is what I fail to undertsand

  • khushbu (8/12/2010)


    e1.startdate or e2.startdate can be used, but dont know why, there is NULL in some rows, while there exists data in all the rows of s.startdate.

    I know NULL can come in last row if e1.startdate and last 2 rows of e2.startdate but in between NUll is what I fail to undertsand

    <<e1.startdate or e2.startdate>> only one is correct. You must decide which.

    There are nulls in other rows because I've joined the table to itself using deccode as well as the rowid. Is the data partitioned on deccode? Can the startdate of one deccode be adjusted then used as the enddate for a different deccode? You must decide.

    “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

  • the data is partitioned on deccode, tarief and startdate

  • khushbu (8/12/2010)


    the data is partitioned on deccode, tarief and startdate

    That's a description of a row, not a partition, which is a group of rows with a common value in one of the columns.

    Your column deccode has two values in the sample you posted. Can a startdate from one deccode be used as the enddate for another?

    “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

  • Thanks for all the help!

    But may be I did not get the partition part .

    No, a startdate from one deccode cannot be used as the enddate for another.

    The end date should always be 1 day less than other row (irrespective of any deccode) start date.

  • khushbu (8/12/2010)


    Thanks for all the help!

    But may be I did not get the partition part .

    No, a startdate from one deccode cannot be used as the enddate for another.

    The end date should always be 1 day less than other row (irrespective of any deccode) start date.

    Ok, so the enddates within one deccode must all be derived from startdates from within the same deccode.

    Next question for you - please define "other row".

    “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

  • posting a sample output required.

    Rowid deccode tarief Startdate Enddate

    1 010127 4300 2005-01-01 2005-12-31

    2 010127 4300 2006-01-01 2007-12-31

    3 010128 4300 2008-01-01 2004-12-31

    4 010229 4300 2005-01-01 2005-12-31

    5 010230 4300 2006-01-01 2099-01-01

    when rowid = 1, start date = 2005-01-01 then for same rowid end date = 2005-12-31 (which came from startdate=2006-01-01 - 1 of rowid = 2 )

    and this cycle repeats

  • khushbu (8/12/2010)


    posting a sample output required.

    Rowid deccode tarief Startdate Enddate

    1 010127 4300 2005-01-01 2005-12-31

    2 010127 4300 2006-01-01 2007-12-31

    3 010128 4300 2008-01-01 2004-12-31

    4 010229 4300 2005-01-01 2005-12-31

    5 010230 4300 2006-01-01 2099-01-01

    when rowid = 1, start date = 2005-01-01 then for same rowid end date = 2005-12-31 (which came from startdate=2006-01-01 - 1 of rowid = 2 )

    and this cycle repeats

    Ok, so you don't partition on deccode. Then the code should look something like this:

    SELECT s.Rowid,

    s.deccode,

    s.tarief,

    s.Startdate,

    Enddate = DATEADD(d,-1, e1.Startdate)

    FROM #MyTable s

    LEFT JOIN #MyTable e1 ON e1.Rowid = s.Rowid + 1

    Note that this is a SELECT, converting it to an UPDATE is simple once it has been checked and verified.

    “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

  • Where does the end-date come from, for rowid 3?

    “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 15 posts - 1 through 15 (of 16 total)

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