how to create a mssql view for getting last state information

Question

lets say, i have two tables, one for object records and one for activity records about these objects.

i'm inserting a new record in this activity table every time an object is inserted or updated.

for telling it in a simple way, assume i have four fields in activity table; objectId, type, status and date.

when an object is about to be updated, i'm planning to get the last state for the object and look for the changes. if there is a difference between the updating value and the previous value, i'll set the value with new input, otherwise i'll set it null. so for example in an update process, user only changes the status value of the object but leaves the type value as the same, so i'll insert a new row with a null value for type and a new value for the status.

SELECT * FROM Activity;

oid   type   status   date
-----------------------------------------
1     0      1        2009.03.05 17:58:07
1     null   2        2009.03.06 07:00:00
1     1      null     2009.03.07 20:18:07
1     3      null     2009.03.08 07:00:00

so i have to create a view tells me the current state of my object like,

SELECT * FROM ObjectStateView Where oid = 1;

oid   type   status   date
-----------------------------------------
1     3      2        2009.03.08 07:00:00

how do i achieve this_?

Answer 1

Have you considered using MAX function?

select oid, type, status, MAX(date) as max_date 
from ObjectStateView 
where oid = 1

Answer 2

Not really sure why you'd want the nulls in there. You can track what's changed between inputs by comparing the latest entry to the previous. Then the current state of the object is the latest entry in the table. You can determine if an object has changed by creating a hash of the parts of the object that you want to track changes to and storing that as an extra column.

Answer 3

Assuming date can be used to find latest record:

CREATE VIEW foo
AS
    SELECT
        A.oid,
        (SELECT TOP 1 type FROM Activity At WHERE At.OID = A.oid AND At.Date <= MAX(A.date) AND type IS NOT NULL),
        (SELECT TOP 1 status FROM Activity Ast WHERE Ast.OID = A.oid AND Ast.Date <= MAX(A.date) AND status IS NOT NULL),
        MAX(A.date) AS date
    FROM
        Activity A
GO

Edit: if you want a JOIN (untested)

CREATE VIEW foo
AS
    SELECT TOP 1
        A.oid,
        At.type,
        Ast.status,
        A.date
    FROM
        Activity A
        LEFT JOIN
        (SELECT TOP 1 oid, date, type FROM Activity WHERE type IS NOT NULL ORDER BY date DESC) At ON A.OID = At.oid
        LEFT JOIN
        (SELECT TOP 1 oid, date, status FROM Activity WHERE status IS NOT NULL ORDER BY date DESC) Ast ON A.OID = Ast.oid
    ORDER BY date DESC
GO

Should have added this earlier:

It will scale exponentially because you have to touch the table 11 different times.

A better solution would be to maintain a "current" table and maintain it via a trigger on activity.

Answer 4

Historical values:

Since you track changes, you may want to see the status of the object historically:

SELECT      a.oid,
            a.date,
            a_type.type,
            a_status.status
FROM        Activity a
LEFT JOIN   Activity a_type
        ON  a_type.oid = a.oid
        AND a_type.date = (SELECT TOP 1 date FROM Activity WHERE oid = a.oid AND date <= a.date AND type IS NOT NULL ORDER BY date DESC)
LEFT JOIN   Activity a_status
        ON  a_status.oid = a.oid
        AND a_status.date = (SELECT TOP 1 date FROM Activity where oid = a.oid AND date <= a.date AND status IS NOT NULL ORDER BY date DESC)

which will return:

oid         date       type        status
----------- ---------- ----------- -----------
1           2009-03-05 0           1
1           2009-03-06 0           2
1           2009-03-07 1           2
1           2009-03-08 3           2

Performance consideration:

On the other hand, if you have more then just a few fields, and the table is big, the performance would become an issue. In this case I would make sense also to store/cache the whole values in another table MyDataHistory, which would contain data like in the table shown above. Then selecting the current(latest) version is trivial using a SQL view filtering the latest row (1 row only) by oid and date.