This topic has been reported for inappropriate content

Post reply

how to set end date

  • November 22, 2019 at 8:14 am

    #3699903

    Hi Sir,

    My requirement is to update the end date using start date.

    The end date should set to one day prior to the start date.

    I want to update end date column and want to display the data for end date as like below.

    SQL Query

    CREATE TABLE Test_Status_Date
    (ID INT,
    STATUE NVARCHAR(40),
    START_DATE   DATE,
    END_DATE DATE)


    INSERT INTO Test_Status_Date VALUES (10,'Active','1921-03-01',NULL);
    INSERT INTO Test_Status_Date VALUES (10,'Suspended','2004-10-01','2005-01-24');
    INSERT INTO Test_Status_Date VALUES (10,'Active','2005-01-01',NULL);
    INSERT INTO Test_Status_Date VALUES (10,'Active','2005-01-24',NULL);

    current out put

    IDSTATUE        START_DATEEND_DATE
    10Active        1921-03-01NULL
    10Suspended2004-10-012005-01-24
    10Terminated2005-01-01NULL
    10Active        2005-01-24NULL

    Expected out put

    IDSTATUE        START_DATEEND_DATE
    10Active1921-03-012004-09-30
    10Suspended2004-10-012004-12-31
    10Terminated2005-01-012005-01-23
    10Active2005-01-24NULL

    Request you please suggest me how to do this?

  • November 22, 2019 at 8:58 am

    #3699907

    You can use the LAG function to get the start date of the next row.  Give it a try, and post back if there's anything in particular you don't understand.

    John

  • November 22, 2019 at 9:05 am

    #3699916

    No Sir, It is not working as I am looking for end date to set prior to start date.

  • November 22, 2019 at 9:11 am

    #3699919

    What is not working?  You haven't shown us what you've already tried.  And please explain what you mean by "to set prior to start date"?  In your expected results, each end date is later than the start date.

    John

  • November 22, 2019 at 10:11 am

    #3699934

    What John said is correct, it works perfectly the way he described it.

    Depending on the real columns you use you also need to use the partition option

    select ID
          ,STATUE
          ,"START_DATE"
          , END_DATE =dateadd(dd,-1,LEAD("START_DATE", 1) OVER (ORDER BY YEAR("START_DATE")))
    from Test_Status_Date

    Output looks like this:

    Unbenannt

     

     

    • This reply was modified 5 years, 1 month ago by  ktflash.

    Attachments:
    You must be logged in to view attached files.

    I want to be the very best
    Like no one ever was

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

  • November 22, 2019 at 10:30 am

    #3699944

    Hi Sir,

    Thanks for your reply and my requirement is as below,

    The first end date of the record is null , so we need to set the seconds row start dates record i.e. 2004-10-01 as a end date but it should be 2004-09-30 which is prior to start date.

     

     

  • November 22, 2019 at 10:34 am

    #3699946

    Sorry, I did not read your comments before posting my comments.

    Yes, It is working fine.

     

    Thanks Sir

  • November 22, 2019 at 10:34 am

    #3699947

    Edit - ignore: posted at the same time as the one above,

Viewing 8 posts - 1 through 7 (of 7 total)

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