Advanced Report Usage Notes: Always Include a Date Filter
To avoid creating a report that might try to work on the entire database you should always include some kind of date filter.
The recommended option is to use the built-in report variables $REPORT_PERIOD_START and $REPORT_PERIOD_END to filter data. If an advanced report contains these variables, the web UI will include date pickers when the user runs the report. A standard filter to find all transfers that were active at any time during the report period would look like the following:
SELECT DISTINCT
ts.id
, ts.contact
, ts.bytes_transferred
FROM
$TBL_TRANSFER_SESSIONS ts
WHERE
ts.started_at < '$REPORT_PERIOD_END'
AND (
ts.stopped_at >= '$REPORT_PERIOD_START'
OR ts.stopped_at IS NULL
);
Note the clause OR ts.stopped_at IS NULL
. Without this, the
report would exclude any transfers that were still running at the time the report
was run. Depending on the intended purpose of the report, you might need to prorate
data for transfers that were active for only part of the reporting period, such as
cases 2, 3, and 4 in the following:
As an alternative, you can avoid the use of $REPORT_PERIOD_START and $REPORT_PERIOD_END if you are creating a report that always looks at the last X hours:
SET @min_start = DATE_SUB(NOW(), INTERVAL 24 HOUR);
CREATE TABLE $FINAL_RESULT_TABLE
SELECT DISTINCT ts.*
FROM
$TBL_TRANSFER_SESSIONS ts
WHERE
ts.started_at >= @min_start
;
SET @dummy = '$REPORT_PERIOD_START';
When running the report, users are then asked for report period dates, but they will be ignored.