2

I need to add a column into a table that contains 3 columns:

  1. GroupKey - Shows the number of an item.
  2. Status - the number of the status that the group was according the insert date. The status number can be changed back and forward several times.
  3. Insert date - the time & date that the status changed.

This 3 columns never change. its like a "log" table , rows can be only added and not deleted or changend

The fourth column (calculated Column) that I need is a flag column that will show if the status was the last time the status has been changed based on the insert_date.

If it is the last status then show 1, else show 0. I hope I explained this well enough. Here is an example:

This is the table.

  • Re your comment the rows are never modified - they will be as IsLastTimeOccured will change from 1 to 0. Hence my-question about initialisation. – MichaelJohn Aug 25 '15 at 9:00
  • 4
    Can you explain why you think you need to store this column, when you can always calculate this at query time given the information that's already in the table? Generally we try to avoid storing redundant data unless there is a very good reason. – Aaron Bertrand Aug 25 '15 at 10:40
4

Another way would be to have a computed column. But the values of this column depend not only on the values of the other columns in the same row but from other rows as well, so this isn't allowed in SQL-Server (and not in any other DBMS I know).

You can still have though such a computed column in a view, with:

create view vlog as
  select log.*,
         IsLastTimeAccured =
           case when inserted_date = 
                     max(inserted_date) over (partition by GroupKey, "Status")
                then 1 else 0
           end 
  from log ;

Test at SQLfiddle. This way, you don't really store any value at all but they have to be actually calculated every time you need them. This may not be as efficient as having it stored as an actual column (and keeping it consistent with a trigger as in @PaulWhite's answer.) You also can't index it. But the rows will not have to be modified when new inserts come and no redundant data is stored.

As pointed by @MikaelEriksson, if you are in SQL-Server 2012+, you can use the LAST_VALUE() or the FIRST_VALUE() window function instead of MAX(), for improved performance. With a supporting index on (GroupKey, [Status], inserted_date desc), the case expression would become:

   case when inserted_date = first_value(inserted_date) over
                               (partition by GroupKey, [Status]
                                order by inserted_date desc
                                rows between unbounded preceding and current row)
        then 1
        else 0
   end
5

This cannot be done with a computed column. One option is to use a trigger:

Sample data

CREATE TABLE dbo.Example
(
    GroupKey integer NOT NULL,
    [Status] integer NOT NULL,
    inserted_date datetime NOT NULL DEFAULT GETUTCDATE(),
    IsLastTimeOccurred bit NOT NULL DEFAULT 'false',

    CONSTRAINT PK_dbo_Example_GroupKey_Status_inserted_date
    PRIMARY KEY CLUSTERED (GroupKey, [Status], inserted_date)
);

INSERT dbo.Example
    (GroupKey, [Status], inserted_date)
VALUES
    (11136, 10, '2015-03-30T13:01:50.153'),
    (11136, 11, '2015-03-30T13:02:03.590'),
    (11136, 12, '2015-03-30T13:02:08.980'),
    (11136, 10, '2015-04-30T09:37:40.477'),
    (11136, 11, '2015-04-30T09:37:46.400'),
    (11136, 12, '2015-04-30T09:38:04.177');

Initial setting

This assumes the rows all have IsLastTimeOccurred = false to begin with:

UPDATE E
SET IsLastTimeOccurred = 'true'
FROM dbo.Example AS E
WHERE NOT EXISTS
(
    SELECT * 
    FROM dbo.Example AS E2
    WHERE E2.GroupKey = E.GroupKey
    AND E2.[Status] = E.[Status]
    AND E2.inserted_date > E.inserted_date
);

Maintenance

The following trigger ensures the column stays correct when the table changes:

CREATE TRIGGER trg_dbo_Example_AIUD
ON dbo.Example
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    -- Holds group keys and the latest insert date
    CREATE TABLE #Changes
    (
        GroupKey integer NOT NULL,
        [Status] integer NOT NULL,
        inserted_date datetime NOT NULL,

        PRIMARY KEY (GroupKey, [Status])
    );

    -- Populate the temporary table    
    INSERT #Changes
    (
        GroupKey, 
        [Status],
        inserted_date
    )
    SELECT
        E.GroupKey, 
        E.[Status],
        MAX(E.inserted_date)
    FROM dbo.Example AS E
    WHERE EXISTS
    (
        SELECT * 
        FROM Inserted AS I
        WHERE I.GroupKey = E.GroupKey
        AND I.[Status] = E.[Status]

        UNION ALL

        SELECT * 
        FROM Deleted AS D
        WHERE D.GroupKey = E.GroupKey
        AND D.[Status] = E.[Status]        
    )
    GROUP BY
        E.GroupKey,
        E.[Status];

    -- Set the flag
    UPDATE E
    SET E.IsLastTimeOccurred =
        CASE WHEN E.inserted_date = C.inserted_date THEN 1 ELSE 0 END
    FROM #Changes AS C
    JOIN dbo.Example AS E
        ON E.GroupKey = C.GroupKey
        AND E.[Status] = C.[Status]
    WHERE
        E.inserted_date = C.inserted_date
        OR E.IsLastTimeOccurred = 'true';
END;

This is a general solution that handles inserts, updates, and deletes. It was written before the question was clarified to state that the table was insert-only. Even so, I would probably leave the full general logic in; it doesn't cost much, and provides a safety net.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.