Remove RTrim because sql server does it automatically and you should repleace NOT IN with NOT EXISTS and also replace the subquery to corelated query.
These changes will enhance query performace.
Originally Posted by STUCK1234
I am comparing 2 tables and trying to retrieve data on any amendments made to the qty in one table and not in the other:
SELECT TRANSACTIONS.order_no, product, whse, unit, Sum(qty)
INNER JOIN ORDERS on
ORDERS.order_no = TRANSACTIONS.order_no
where status = '6'
group by product, whse, TRANSACTIONS.order_no, unit,date_required
RTrim(TRANSACTIONS.order_no)+RTrim(product)+RTrim( whse)+RTrim(unit)+CAST(Sum(qty)AS varchar) not in
(select RTrim(d.order_no)+RTrim(product)+RTrim(d.warehouse )+RTrim(dbo.toProperCase(unit_of_sale))+CAST(Sum(a llocated_qty) AS varchar)
from det d
INNER JOIN head h on
h.order_no = d.order_no
where h.status = '6'
group by product, d.warehouse, d.order_no, d.unit_of_sale)
this SEEMS 2 wrk, but is there a quicker and better way of doin the above query?