Post reply

Update Start date using end date.

  • November 26, 2019 at 12:17 pm

    #3701027

    Hi Sir,

    I want to set the END_DATE columns value whose status is "Suspended", into the column STATUS_CHANGE_DATE whose status is "Active", except first row.Please suggest me how to update these values?.

    --create table script--

    CREATE TABLE temp_date
    (ID INT,
    STATUS NVARCHAR(10),
    STATUS_CHANGE_DATE DATE,
    START_DATE INT,
    END_DATE INT,
    ROW_NUM INT);
         
    --Insert Into script---

    INSERT INTO temp_date VALUES (123,'Active','1991-01-09',19910109,NULL,1);
    INSERT INTO temp_date VALUES (234,'Suspended','1997-03-13',19970313,19970507,2);
    INSERT INTO temp_date VALUES (456,'Active','1997-05-07',NULL,NULL,3);
    INSERT INTO temp_date VALUES (678,'Suspended','2000-01-01',20000101,20000328,4);
    INSERT INTO temp_date VALUES (890,'Active','2000-03-28',NULL,NULL,5);

    My Current out put is as

    IDSTATUS     STATUS_CHANGE_DATESTART_DATEEND_DATEROW_NUM
    123Active1991-01-0919910109NULL1
    234Suspended1997-03-1319970313199705072
    456Active1991-01-09NULLNULL3
    678Suspended2000-01-0120000101200003284
    890Active1991-01-09NULLNULL5

    Expected Output should be

     

    IDSTATUSSTATUS_CHANGE_DATESTART_DATEEND_DATEROW_NUM
    123Active1991-01-0919910109NULL1
    234Suspended1997-03-1319970313199705072
    456Active1997-05-07NULLNULL3
    678Suspended2000-01-0120000101200003284
    890Active2000-03-28NULLNULL5

  • November 26, 2019 at 1:04 pm

    #3701045 

    -- SQL 2012 and later
    ; WITH prevs AS (
       SELECT *, LAG(end_date) OVER (ORDER BY START_DATE) AS prev_end_date
       FROM   temp_date
    )
    UPDATE prevs
    SET    STATUS_CHANGE_DATE = convert(char(8), prev_end_date)
    WHERE  STATUS = 'active'
      AND  prev_end_date IS NOT NULL   
    go
    -- SQL 2008 and SQL 2005
    ; WITH numbering AS (
       SELECT *, row_number() OVER( ORDER BY START_DATE) AS rowno
       FROM   temp_date
    )
    UPDATE a
    SET    STATUS_CHANGE_DATE = convert(char(8), b.END_DATE)
    FROM   numbering a
    JOIN   numbering b ON b.rowno = a.rowno - 1

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • November 26, 2019 at 2:33 pm

    #3701086

    No sir it is not working properly to get the proper output

  • November 26, 2019 at 3:30 pm

    #3701097

    I believe that I checked the output, but I don't have that query window set up any more. Since I am short on time, could you care to explain that what is not to your expectations?

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • November 26, 2019 at 3:39 pm

    #3701104

    Please do not post to multiple forums.  It fragments the conversation.  Please respond on https://www.sqlservercentral.com/forums/topic/update-start-date-using-end-date-3

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • November 26, 2019 at 5:53 pm

    #3701167

    Closing this. Please post at: https://www.sqlservercentral.com/forums/topic/update-start-date-using-end-date-3

Viewing 6 posts - 1 through 5 (of 5 total)

The topic ‘Update Start date using end date.’ is closed to new replies.