Post reply

Update problem

  • August 16, 2005 at 6:00 am

    #363464

    Hi,

    I'd like to update the FromStatus column with values from the previous rows of the ToStatus column. When the EndDate occurs FromStatus column of the next row should not be updated.

    id

    		claimreferenceFromStatusToStatusStartDateEndDate
    1DMARP00240NULLNew Claim06/12/200421/12/2004
    2DMARP00240NULLApplication Form Sent21/12/200417/05/2005
    3DMARP00240NULLApplication Form Received17/05/200517/05/2005
    4DMARP00240NULLApplication Form Received17/05/200523/05/2005
    5DMARP00240NULLInformation Requested23/05/200523/05/2005
    6DMARP00240NULLInformation Requested23/05/200513/06/2005
    7DMARP00240NULLInformation Received13/06/200513/06/2005
    8DMARP00240NULLInformation Received13/06/200512/07/2005
    9DMARP00240NULLInformation Under Review12/07/200512/07/2005
    10DMARP00240NULLInformation Under Review12/07/200518/07/2005
    11DMARP00240NULLAssistant Manager Review18/07/200518/07/2005
    12DMARP00240NULLAssistant Manager Review18/07/200518/07/2005
    13DMARP00240NULLOffer Sent18/07/200522/07/2005
    14DMARP00240NULLOffer Accepted22/07/200526/07/2005
    15DMARP00240NULLCompleted26/07/200526/07/2005
    16DMARP00240NULLCompleted26/07/2005NULL
    17DMARP01039NULLNew Claim06/12/200421/12/2004
    18DMARP01039NULLApplication Form Sent21/12/200410/01/2005

    The results that i'm looking for are:

    idclaimreferenceFromStatusToStatusStartDateEndDate
    1DMARP00240NULLNew Claim06/12/200421/12/2004
    2DMARP00240New ClaimApplication Form Sent21/12/200417/05/2005
    3DMARP00240Application Form SentApplication Form Received17/05/200517/05/2005
    4DMARP00240Application Form ReceivedApplication Form Received17/05/200523/05/2005
    5DMARP00240Application Form ReceivedInformation Requested23/05/200523/05/2005
    6DMARP00240Information RequestedInformation Requested23/05/200513/06/2005
    7DMARP00240Information RequestedInformation Received13/06/200513/06/2005
    8DMARP00240Information ReceivedInformation Received13/06/200512/07/2005
    9DMARP00240Information ReceivedInformation Under Review12/07/200512/07/2005
    10DMARP00240Information Under ReviewInformation Under Review12/07/200518/07/2005
    11DMARP00240Information Under ReviewAssistant Manager Review18/07/200518/07/2005
    12DMARP00240Assistant Manager ReviewAssistant Manager Review18/07/200518/07/2005
    13DMARP00240Assistant Manager ReviewOffer Sent18/07/200522/07/2005
    14DMARP00240Offer SentOffer Accepted22/07/200526/07/2005
    15DMARP00240Offer AcceptedCompleted26/07/200526/07/2005
    16DMARP00240CompletedCompleted26/07/2005NULL
    17DMARP01039NULLNew Claim06/12/200421/12/2004
    18DMARP01039New ClaimApplication Form Sent21/12/200410/01/2005

    I have looked into cursors but would like to avoid them if possible as i have a large table to update. Any help would be much appreciated

    Chet

  • August 16, 2005 at 6:21 am

    #582399

    Pls Can u explain more abt it

    I want to know when u want to cheange the data

    It will b better if u tell the senario.

     

  • August 16, 2005 at 6:30 am

    #582403

    Yup, is it for reporting or for a permanent table. If it's for a table then I strongly suggest against that design as it is denormalized. A simple self join will provide the previous status when needed.

  • August 16, 2005 at 7:53 am

    #582446

    Hi,

    This is for a permanent table. The scenarios is the FromStatus column has all NULLS. it needs to be updated using the previous values from ToStatus column. for example,

    id

    		claimreferenceFromStatusToStatusStartDateEndDate
    1DMARP00240NULLNew Claim06/12/200421/12/2004
    2DMARP00240NULLApplication Form Sent21/12/200417/05/2005

    After update the FromStatus in row2 (or where id=2) should be NewClaim.

    Also when the EndDate column is NULL in row16 below:

    16DMARP00240NULLCompleted26/07/2005        NULL
    17DMARP01039NULLNew Claim06/12/200421/12/2004
    18DMARP01039NULLApplication Form Sent21/12/200410/01/2005

    Then in row 17 the FromStatus should not be updated and in row 18 normal update resumes from ToStatus value of row 17 = New Claim

    Hope this makes it clearer.

    Chet

  • August 16, 2005 at 8:19 am

    #582467

    Same problem... it seems like a flawed design. It can be done with a self join.

  • August 16, 2005 at 10:35 pm

    #582691

    --Hi,

    --Following SCRIPT will serve your purpose.

    DECLARE @ID int

     SELECT @ID = Max(ID) From Sheet

     --SELECT @ID

     WHILE @ID <> 0

     BEGIN

      UPDATE Sheet Set

       FromStatus

        = (

         SELECT

          CASE IsNull(EndDate,1)

           WHEN  1 THEN  'NULL'

           ELSE ToStatus

          END

         FROM

          Sheet

         WHERE

          ID = @ID

          )

      WHERE ID = @ID + 1

      SET @ID = @ID - 1

     END

     

    --Any doubt, regarding the same, feel free 2 ask.

     

    --Vivek

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • August 17, 2005 at 3:52 am

    #582716

    Hi Vivek,

    Cheers mate - this works really well . Thanks once again...

    Chet

  • August 17, 2005 at 8:00 am

    #582747

    As Remi mentioned design can be different and better.

    Anyway for existing design updating using a loop will be a performance problem for large data rows. I would do it like

    SET NOCOUNT ON

    DECLARE @MyTable TABLE

    (

    MyID  INT IDENTITY,

    claimreference VARCHAR(20),

    FromStatus  VARCHAR(50),

    ToStatus VARCHAR(50),

    StartDate DATETIME,

    EndDate  DATETIME

    )

    SET DATEFORMAT DMY

    INSERT @MyTable (claimreference, FromStatus, ToStatus, StartDate, EndDate)

    SELECT 'DMARP00240', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Sent', '21/12/2004', '17/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '17/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '23/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '23/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '13/06/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '13/06/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '12/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '12/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Offer Sent', '18/07/2005', '22/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Offer Accepted', '22/07/2005', '26/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', '26/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', NULL  UNION ALL

    SELECT 'DMARP01039', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

    SELECT 'DMARP01039', NULL, 'Application Form Sent', '21/12/2004', '10/01/2005'

     

    UPDATE A

    SET

     FromStatus = B.ToStatus

    FROM

     @MyTable A

    JOIN

     @MyTable B

    ON

     A.MyID = B.MyID + 1

    WHERE

     A.ToStatus <> 'New Claim'

     

    SELECT * FROM @MyTable

    Regards,
    gova

  • August 18, 2005 at 12:57 am

    #582922

    Hi Remi ,

              The above query will work fine with the above data .If a record exists with 'New Claim' and Valid EndDate Value.Then it will fail. I think following will work.

     

    SET NOCOUNT ON

    DECLARE @MyTable TABLE

    (

    MyID  INT IDENTITY,

    claimreference VARCHAR(20),

    FromStatus  VARCHAR(50),

    ToStatus VARCHAR(50),

    StartDate DATETIME,

    EndDate  DATETIME

    )

    SET DATEFORMAT DMY

    INSERT @MyTable (claimreference, FromStatus, ToStatus, StartDate, EndDate)

    SELECT 'DMARP00240', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Sent', '21/12/2004', '17/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '17/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '23/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '23/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '13/06/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '13/06/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '12/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '12/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Offer Sent', '18/07/2005', '22/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Offer Accepted', '22/07/2005', '26/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', '26/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', NULL  UNION ALL

    SELECT 'DMARP01039', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

    SELECT 'DMARP01039', NULL, 'Application Form Sent', '21/12/2004', '10/01/2005'

     

    UPDATE A

    SET

     FromStatus = Case B.EndDate when null then null else B.ToStatus end

    FROM

     @MyTable A

    JOIN

     @MyTable B

    ON

     A.MyID = B.MyID + 1

     

    Thanks,

    Murthy

  • August 18, 2005 at 2:37 am

    #582932

    Join is generally performancce degrader. Select Case is faster and executes in less time compared to JOIN. For faster execution, avoid join. Better table design is something I appreciate but  

    your query fails requirement of ChetShah, if you have the following

     

    SET NOCOUNT ON

    DECLARE @MyTable TABLE

    (

    MyID  INT IDENTITY,

    claimreference VARCHAR(20),

    FromStatus  VARCHAR(50),

    ToStatus VARCHAR(50),

    StartDate DATETIME,

    EndDate  DATETIME

    )

    SET DATEFORMAT DMY

    INSERT @MyTable (claimreference, FromStatus, ToStatus, StartDate, EndDate)

    SELECT 'DMARP00240', NULL, 'New Claim', '06/12/2004', '21/12/2004' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Sent', '21/12/2004', '17/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '17/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Application Form Received', '17/05/2005', '23/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '23/05/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Requested', '23/05/2005', '13/06/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '13/06/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Received', '13/06/2005', '12/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '12/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Information Under Review', '12/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Assistant Manager Review', '18/07/2005', '18/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Offer Sent', '18/07/2005', '22/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Offer Accepted', '22/07/2005', '26/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', '26/07/2005' UNION ALL

    SELECT 'DMARP00240', NULL, 'Completed', '26/07/2005', NULL  UNION ALL

    SELECT 'DMARP01039', NULL, 'Offer Sent', '06/12/2004', '21/12/2004' UNION ALL

    SELECT 'DMARP01039', NULL, 'Application Form Sent', '21/12/2004', '10/01/2005'

     

    UPDATE A

    SET

     FromStatus = B.ToStatus

    FROM

     @MyTable A

    JOIN

     @MyTable B

    ON

     A.MyID = B.MyID + 1

    WHERE

     A.ToStatus <> 'New Claim'

     

    SELECT * FROM @MyTable

    Best Regards,

    Vivek

     

    /**A strong positive mental attitude will create more miracles than any wonder drug**/

  • August 18, 2005 at 7:56 am

    #583025

    Back to square

    Regards,
    gova

  • August 18, 2005 at 7:59 am

    #583028

    Stupid question here :

    What happens when a row is deleted or when the previous row is updated??

    How do you propagate the changes to the next row?

  • August 18, 2005 at 8:05 am

    #583039

    Stupid Answer.

    Write a trigger to take care of that.

    Remi as mentioned in your first answer the design is not correct. There is no need for that FromStatus & One of the date columns.

     

    Regards,
    gova

  • August 18, 2005 at 8:10 am

    #583044

    I know that... just wanted to be sure that he knew that too. .

Viewing 14 posts - 1 through 13 (of 13 total)

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