SQL Stock Opname

mail@pastecode.io avatar
unknown
sql
10 months ago
2.5 kB
2
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