Categories
Uncategorized

Maintenance MySQL – Asset Report Android: asset_report_last

1. asset_report_last_sub
CREATE VIEW asset_report_last_sub AS SELECT ar_barcode, MAX(ar_ts) ar_ts FROM asset_report GROUP BY ar_barcode;

2. asset_report_last
CREATE VIEW asset_report_last AS
SELECT t.ar_id, t.ar_barcode, t.ar_datedue, t.ar_itemstatus, t.ar_actiontaken, t.ar_actionleft, t.ar_userid, t.ar_ts, t.ar_scandt
FROM asset_report t
LEFT JOIN asset_report_last_sub x
ON x.ar_barcode=t.ar_barcode AND x.ar_ts=t.ar_ts
WHERE x.ar_ts=t.ar_ts;