Maintenance MySQL – Stock Report Android: stock_record_count

1. stock_record_count_sublocal
CREATE VIEW stock_record_count_sublocal AS
SELECT sr_barcode barcode, SUM(sr_qty) qtyused
FROM stock_record
GROUP BY barcode;

2. stock_record_count_subunion
CREATE VIEW stock_record_count_subunion AS
(SELECT barcode, qtyused FROM jobsheet_report_total)
union all
(SELECT barcode, qtyused FROM stock_record_count_sublocal);

3. stock_record_count
CREATE VIEW stock_record_count AS
SELECT
item_list.il_barcode,
item_list.il_description,
item_list.il_group,
item_list.il_priloc,
item_list.il_secloc,
item_list.il_terloc,
SUM(stock_record_count_subunion.qtyused* -1) stock_level
FROM item_list
INNER JOIN stock_record_count_subunion
ON item_list.il_barcode=stock_record_count_subunion.barcode
GROUP BY item_list.il_barcode;