Rows changed since a time (template, runs on WORLD)

ANYRead onlyScripts Gallery

A check to run on WORLD, then adapt: the rows modified at or after a given time, for a table with a modification timestamp. On WORLD, the countries changed since a date.

Verification status. Written from the BroadSQL documentation and the ANY documentation. It has not been run by the BroadSQL team: run it first on a test database, and read its result critically.

When to use it

What changed during an incident window.
What a nightly job touched.

The Script

Download rows-changed-since.sql

-- @description: Rows changed since a date and time (on WORLD: countries; adapt COUNTRY and LAST_UPDATE)
-- @tags: data quality, audit, incident, template, world
-- @status: draft
-- @access: read
-- @dialect: ANY
-- @param since TIMESTAMP "Rows changed at or after this time, as 'yyyy-MM-dd HH:mm:ss'"
-- On WORLD, COUNTRY.LAST_UPDATE holds the time of each row's last change. For your own data, replace COUNTRY,
-- CODE and NAME with your table and its columns, and LAST_UPDATE with its last modification column.
SELECT CODE, NAME, LAST_UPDATE
  FROM COUNTRY
 WHERE LAST_UPDATE >= ${since}
 ORDER BY LAST_UPDATE;

Parameters

Parameter	Type	Default	Meaning
`since`	`TIMESTAMP`	Required	Rows changed at or after this time, as 'yyyy-MM-dd HH:mm:ss'

Install and run it

Save the file in your Scripts Library folder (scripts in the installation folder by default), for example as gallery/rows-changed-since.sql.
Open the sample with CONNECT WORLD; and run it as it is. For your own data, copy the file and replace COUNTRY, its columns and LAST_UPDATE with your table and its modification column.
Check what it declares: LIB SHOW gallery/rows-changed-since.sql;
Run it: @gallery/rows-changed-since.sql since='2015-07-01 00:00:00';

What you get

The changed rows, oldest change first. On WORLD, since='2015-07-01 00:00:00' returns 15 rows: countries changed on 8 July 2015, such as South Sudan, Serbia and Montenegro, and two test rows that the sample itself contains.

Limits

The table must have a column holding the time of the last change.
A TIMESTAMP is written 'yyyy-MM-dd HH:mm:ss', without a time zone.
On Apache Derby, the driver refuses date values sent as parameters: declare the parameter TEXT and write CAST(${since} AS TIMESTAMP).
The values were computed on the WORLD sample with the H2 engine; the Script itself was not run through BroadSQL by the BroadSQL team.

Found something wrong or unclear on this page? Tell us, with the page address.