Post reply

Compare value changes by Week Number per unique ID

  • March 22, 2019 at 5:12 am

    #371761

    Hi,

    I have a requirement to compare the values (based on current week and previous week) for a column by comparing the Week number column (example - 201901) and then populate the Comments column if the value has changed.

    For example, lets say for a certain Unique ID if the current week is 201906 and the Author column has a certain value, I need to compare the Author value from the previous week - 201905 for the same unique ID if it has changed by mistake. 

    If the value has changed, then need to populate the Comments column.

    Does anyone please know how to go about this ?  Any suggestions will be welcomed. Thanks.

  • March 22, 2019 at 5:57 am

    #2025189

    Could you provide us with some sample data and what the results you are after look like?

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • March 22, 2019 at 6:26 am

    #2025190

    Hi,

    Please find the DDL and sample data below.

    CREATE TABLE TABLE1
    (
    ID INT,
    WEEK_NUMBER VARCHAR(10),
    AUTHOR NVARCHAR(255),
    COMMENTS NVARCHAR(500)
    )

    INSERT INTO TABLE1
    VALUES('1','201905','ABC','')
    INSERT INTO TABLE1
    VALUES('1','201906','CAB','AUTHOR NAME HAS CHANGED FROM PREVIOUS WEEK')

    In the example above for ID = 1 the Author value has changed in Week Number - 201906 from ABC to CAB.
    So this is what I need to achieve and then also add a comment in the Comments column. Thanks.

  • March 22, 2019 at 6:34 am

    #2025192

    You can use LAG to check the value of the previous row within a CASE expression and show the comment as needed:

    SELECT ID,
        WEEK_NUMBER,
       AUTHOR,
       CASE WHEN ISNULL(LAG(AUTHOR) OVER (PARTITION BY ID ORDER BY WEEK_NUMBER ASC),'') != ISNULL(Author,'') THEN 'AUTHOR NAME HAS CHANGED FRO PREVIOUS WEEK' ELSE '' END AS COMMENTS
    FROM dbo.TABLE1;

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

Viewing 4 posts - 1 through 3 (of 3 total)

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