TAAS

Query - Total Price And Cost

SELECT
  MIN(sm.pk_sale_master_id) AS pk_sale_master_id,
  COUNT(doc.vhr_file_number) AS vhr_file_number_count,
  SUM(sm.dbl_base_cur_supplier_amount) AS dbl_base_cur_supplier_amount,
  SUM(sm.dbl_base_cur_customer_amount) AS dbl_base_cur_customer_amount,
  'SALE' AS vhr_module
FROM sale.tbl_sale_master AS sm
LEFT JOIN document.tbl_documents AS doc
    ON sm.fk_documents_id =  doc.pk_documents_id
WHERE
  UPPER(doc.vhr_file_number) = '?'
GROUP BY doc.vhr_file_number

UNION ALL

SELECT
  MIN(rm.pk_refund_master_id) AS pk_refund_master_id,
  COUNT(doc.vhr_file_number) AS vhr_file_number_count,
  SUM(rm.dbl_base_cur_supplier_amount) AS dbl_base_cur_supplier_amount,
  SUM(rm.dbl_base_cur_customer_amount) AS dbl_base_cur_customer_amount,
  'REFYND' AS vhr_module
FROM refund.tbl_refund_master AS rm
LEFT JOIN document.tbl_documents AS doc
    ON rm.fk_documents_id =  doc.pk_documents_id
WHERE UPPER(doc.vhr_file_number) = '?'
GROUP BY doc.vhr_file_number