Databases
StackOverflow2013
Postsdbo
POSQL standard select current records from an audit log question
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL standard select current records from an audit log question
    text
    copied!<p>My memory is failing me. I have a simple audit log table based on a trigger:<br/><br/> <kbd>ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>int (identity, PK)</kbd><br/> <kbd>CustomerID&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>int&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><br/> <kbd>Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>varchar(255)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><br/> <kbd>Address&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>varchar(255)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><br/> <kbd>AuditDateTime&nbsp;</kbd><kbd>datetime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><br/> <kbd>AuditCode&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>char(1)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><br/><br/><br/> It has data like this:<br/><br/> <kbd>ID&nbsp;</kbd><kbd>CustomerID&nbsp;</kbd><kbd>Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Address&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>AuditDateTime&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>AuditCode&nbsp;</kbd> <kbd>1&nbsp;&nbsp;</kbd><kbd>123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Bob&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>123 Internet Way&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:18:06.353</kbd><kbd>I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>2&nbsp;&nbsp;</kbd><kbd>123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Bob&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>123 Internet Way&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:19:02.117</kbd><kbd>D&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>3&nbsp;&nbsp;</kbd><kbd>123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Jerry&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>123 Internet Way&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:36:03.517</kbd><kbd>I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>4&nbsp;&nbsp;</kbd><kbd>123&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Bob&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>123 My Edited Way&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:36:08.050</kbd><kbd>U&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>5&nbsp;&nbsp;</kbd><kbd>100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Arnold&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>100 SkyNet Way&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:36:18.607</kbd><kbd>I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>6&nbsp;&nbsp;</kbd><kbd>100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Nicky&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>100 Star Way&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:36:25.920</kbd><kbd>U&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>7&nbsp;&nbsp;</kbd><kbd>110&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Blondie&nbsp;&nbsp;&nbsp;</kbd><kbd>110 Another Way&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>2009-07-17 13:36:42.313</kbd><kbd>I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd> <kbd>8&nbsp;&nbsp;</kbd><kbd>113&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>Sally&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><kbd>113 Yet another Way&nbsp;</kbd><kbd>2009-07-17 13:36:57.627</kbd><kbd>I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</kbd><br/> <br/><br/> <strong>What would be the efficient select statement be to get all most current records between a start and end time?</strong> FYI: I for insert, D for delete, and U for update.<br/><br/> Am I missing anything in the audit table? My next step is to create an audit table that only records changes, yet you can extract the most recent records for the given time frame. For the life of me I cannot find it on any search engine easily. Links would work too. Thanks for the help.</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