SQL Stock Opname
sql
4 days ago
2.5 kB
1
Indexable
Never
SELECT products.code, products.name, ( COALESCE( ( SELECT SUM(rc_detail.quantity) FROM tr_receive_detail AS rc_detail INNER JOIN tr_receive AS rc ON rc_detail.receive_code = rc.receive_code WHERE rc.receive_date < '2023-09-19' AND rc_detail.product_code = products.code GROUP BY rc_detail.product_code ), 0 ) - COALESCE( ( SELECT SUM(trans_detail.quantity) FROM tr_transaction_detail AS trans_detail INNER JOIN tr_transaction AS trans ON trans_detail.invoice_no = trans.invoice_no WHERE trans.trans_date < '2023-09-19' AND trans_detail.product_code = products.code GROUP BY trans_detail.product_code ), 0 ) ) AS qty_begin, COALESCE( ( SELECT SUM(rc_detail.quantity) FROM tr_receive_detail AS rc_detail INNER JOIN tr_receive AS rc ON rc_detail.receive_code = rc.receive_code WHERE (rc.receive_date BETWEEN '2023-09-19' AND '2023-09-19') AND rc_detail.product_code = products.code GROUP BY rc_detail.product_code ), 0 ) AS qty_in, COALESCE( ( SELECT SUM(trans_detail.quantity) FROM tr_transaction_detail AS trans_detail INNER JOIN tr_transaction AS trans ON trans_detail.invoice_no = trans.invoice_no WHERE (trans.trans_date BETWEEN '2023-09-19' AND '2023-09-19') AND trans_detail.product_code = products.code GROUP BY trans_detail.product_code ), 0 ) AS qty_out, ( COALESCE( ( SELECT SUM(rc_detail.quantity) FROM tr_receive_detail AS rc_detail INNER JOIN tr_receive AS rc ON rc_detail.receive_code = rc.receive_code WHERE rc.receive_date <= '2023-09-19' AND rc_detail.product_code = products.code GROUP BY rc_detail.product_code ), 0 ) - COALESCE( ( SELECT SUM(trans_detail.quantity) FROM tr_transaction_detail AS trans_detail INNER JOIN tr_transaction AS trans ON trans_detail.invoice_no = trans.invoice_no WHERE trans.trans_date <= '2023-09-19' AND trans_detail.product_code = products.code GROUP BY trans_detail.product_code ), 0 ) ) AS qty_end FROM products WHERE deleted_at IS NULL ORDER BY products.name ASC, products.code ASC