|Appendix / Advanced Report Usage Notes|
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.