|id| VoucherNO| VoucherDate|
+--+----------+------------+
|1 | 0001 | 28/9/2017 |
|2 | 0010 | 27/9/2017 |
|id | ITEMID | QTYpurchased | voucher
+---+--------+--------------+--------
|1 | 101 | 12 | 0001
|2 | 120 | 25 | 0001
|3 | 121 | 21 | 0014
|id| VoucherNO| VoucherDate|
+--+----------+------------+
|1 | 0025 | 25/9/2017 |
|2 | 0058 | 23/9/2017 |
|id | ITEMID | QTYpurchased | voucher
+---+--------+--------------+--------
|1 | 101 | 8 | 0025
|2 | 120 | 5 | 0025
|3 | 121 | 10 | 0058
SELECT
A.ITEMN,
A.ITEMNAME,
SUM(CAST(C.QUANTITY AS numeric(18,2))) - SUM(CAST(A.QTY AS numeric(18,2))) as QTY
FROM DETAILSALE A, SALESREPORT B, DETAILPURCHASES C, PURCHASEREPORT D
WHERE A.BARCODE = B.VOUCHERNO
AND C.BARCODE = D.VOUCHERNO
AND D.VOUCHERDATE=B.VOUCHERDATE
AND D.VOUCHERDATE BETWEEN '" & DATE1.Text & "' AND '" & DATE2.Text & "'
GROUP BY A.ITEMN,A.ITEMNAME
ORDER BY A.ITEMN ASC
| ITEMID | QTY |
+--------+-----+
| 101 | 2 |
| 120 | 10 |
| 121 | 10 |
Aggregate first and only join then. I am missing a product table from your description and names in your table descriptions and query don't match, so you may have to adjust names in my query:
select
product.id,
product.name,
purchases.total,
sales.total
from product
left join
(
select itemid, sum(quantity) as total
from detail_purchase
where voucher in (select voucher from purchase where date between date '2017-09-01'
and date '2017-09-05')
group by itemid
) purchases on purchases.itemid = product.itemid
left join
(
select itemid, sum(quantity) as total
from detail_sale
where voucher in (select voucher from sale where date between date '2017-09-01'
and date '2017-09-05')
group by itemid
) sales on sales.itemid = product.itemid;
Change outer joins to inner joins, if you only want products that where bought and/or sold.