StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Another (better?) method to keep audit history is to use a 'startDate' and 'endDate' column rather than an auditDateTime and AuditCode column. This is often the approach in tracking Type 2 changes (new versions of a row) in data warehouses.</p> <p>This lets you more directly select the current rows (WHERE endDate is NULL), and you will not need to treat updates differently than inserts or deletes. You simply have three cases:</p> <ul> <li>Insert: copy the full row along with a start date and NULL end date</li> <li>Delete: set the End Date of the existing current row (endDate is NULL)</li> <li>Update: do a Delete then Insert</li> </ul> <p>Your select would simply be:</p> <pre><code>select * from AuditTable where endDate is NULL </code></pre> <p>Anyway, here's my query for your existing schema:</p> <pre><code>declare @from datetime declare @to datetime select b.* from ( select customerId max(auditdatetime) 'auditDateTime' from AuditTable where auditcode in ('I', 'U') and auditdatetime between @from and @to group by customerId having /* rely on "current" being defined as INSERTS > DELETES */ sum(case when auditcode = 'I' then 1 else 0 end) > sum(case when auditcode = 'D' then 1 else 0 end) ) a cross apply( select top 1 customerId, name, address, auditdateTime from AuditTable where auditdatetime = a.auditdatetime and customerId = a.customerId ) b </code></pre> <p><strong>References</strong></p> <p>A <a href="http://www.simple-talk.com/sql/sql-training/sql-server-data-warehouse-cribsheet/" rel="nofollow noreferrer">cribsheet for data warehouses</a>, but has a good section on type 2 changes (what you want to track)</p> <p>MSDN page on <a href="http://msdn.microsoft.com/en-us/library/aa902672(SQL.80).aspx#sql_dwdesign_topic4" rel="nofollow noreferrer">data warehousing</a></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload