Query help when using audit table

Question

Assuming I have two tables, one with the following columns called lease_period:

tenant_trading_name, suite_id, lease_id, building_id

and another, called lease_period_audit with the following:

audit_date, audit_type, tenant_trading_name, suite_id, lease_id, building_id

Each time a record is updated on lease_period and entry is made on lease_period_audit with a status of 'Updated'. I'm trying to find all updates made only to the tenant_trading_name field but haven't had any success. So far I have the following:

select              lpa.*
from                property.lease_period_audit lpa
inner join          property.lease_period lp on lpa.suite_id = lp.suite_id and lpa.lease_id = lp.lease_id and lpa.building_id = lp.building_id
where               audit_type = 'Updated'
                    and lp.tenant_trading_name <> lpa.tenant_trading_name
order by            1 desc

Where's the flaw in my thought process here? How can this be done / how should I be thinking about this?

@rogelware Unfortunately this is for work so that isn't an option (I didn't create the database structure). — Michael A, Jan 07 '12 at 06:36
what's the primary key of the tables? Can the suite_id, lease_id, building_id be changed on the LP table? Are there other fields in the LP or LPA table? — Rogel Garcia, Jan 07 '12 at 13:40
Maybe you can't change the structure, but you can probably shed more light on it. Without knowing what column(s) of `lease_period_audit` is (are) the key of `lease_period`, how can we know whether two different rows in the audit table are changes to the same row of the original table or to its two distinct rows? — Andriy M, Jan 07 '12 at 20:00
@AndriyM For the sake of simplicity let's assume they both have a primary key column called 'id' with a unique numerical value. Please let me know anything else I can provide to help you answer — Michael A, Jan 08 '12 at 02:15
Thanks, so there *is* a primary key in `lease_period`. But is it logged in the audit table along with other columns? — Andriy M, Jan 08 '12 at 04:28
If the primary key is (suite_id, lease_id, building_id) and one of its value can be changed, you can't do what you're trying to do. In this case, fire your DBA. — Rogel Garcia, Jan 08 '12 at 05:34
rogelware, how would you craft this should there be a primary key that can't be changed? I'm sure there is for this table and will check with the dba's once I'm in on Monday — Michael A, Jan 08 '12 at 22:05
If the primary key is changed you can't know which register was updated. The log made for a register that had the primary key changed is lost. Quote Andriy: "how can we know whether two different rows in the audit table are changes to the same row of the original table or to its two distinct rows?" (this question can't be answered if primary key is changed) — Rogel Garcia, Jan 09 '12 at 03:56
@rogelware: I might be wrong, but in his last comment Michael seemed not to be arguing but rather to actually be asking for advice. — Andriy M, Jan 10 '12 at 03:00

score 2 · Accepted Answer · answered Jan 10 '12 at 03:53

Assuming the audit table also logs the lease_period primary key column, referenced here lp_id for simplicity, you could try the following approach:

Find all the rows where audit_type is 'Updated'.
Rank all the rows by audit_date and partitioning them by lp_id.
Rank the rows by audit_date partitioning by lp_id, suite_id, lease_id, building_id.
Get the difference between the two rankings.
Rank the rows again by audit_date, partitioning them now by lp_id, suite_id, lease_id, building_id, (ranking_difference).
Output all the rows where the last ranking value is 2 or greater.

The first four steps result in a row set where each group of consecutive (in ascending order of audit_date) rows with identical values of suite_id, lease_id, building_id for the same lp_id will be uniquely distinguished by a value calculated as the difference between the rankings #2 & #3.

Within the group, every row, starting from the second one, will differ from the previous one only in the value of tenant_trading_name, which is just what we need. So, we rank the rows once again, taking into account the 'group ID' we've just obtained, then return every row with the ranking of 2 or higher.

Here's an approximate implementation:

WITH marked AS (
  SELECT
    *,
    grp = ROW_NUMBER() OVER (PARTITION BY lp_id
                                 ORDER BY audit_date)
        - ROW_NUMBER() OVER (PARTITION BY lp_id, suite_id, lease_id, building_id
                                 ORDER BY audit_date)
  FROM lease_period_audit
  WHERE audit_type = 'Updated'
),
ranked AS (
  SELECT
    *,
    rnk = ROW_NUMBER() OVER (PARTITION BY lp_id, suite_id, lease_id, building_id, grp
                                 ORDER BY audit_date)
  FROM marked
)
SELECT
  audit_date,
  lp_id,
  tenant_trading_name,
  suite_id,
  lease_id,
  building_id
FROM ranked
WHERE rnk = 2

Note. This assumes that the audit table logs only real changes, i.e. there can't be two consecutive rows with the same primary key where all four columns have identical values.

Rogel Garcia · Answer 2 · 2012-01-07T13:35:53.540

You should think something like this (pseudo-code):

edit: I haven't realized before that the lpa table actually has all the data, there's no need to join with LP

select lpa.*
from   lpa 
join   lpa_before on 
           lpa_before.id = lpa.id and 
           lpa_before.date = 
                  (select max(date) from lpa3 where lpa3.date < lpa.date and lpa.id = lpa3.id)
where  auditytype = 'update' and lpa.name <> lpa_before.name

I hope I could explain.. (it's not a trivial solution, but thats what comes to my mind)

In English:

Select the LPA registers. Join with another LPA that will be the LPA just before the original one. Call this LPA_BEFORE. To join the LPA_BEFORE you have to compare all ids from LPA and LPA_BEFORE, and the LPA_BEFORE date must be the maximum from the LPAs which have a date less than the original LPA. Compare the names of the LPA with LPA_BEFORE

Query help when using audit table

2 Answers2

Linked