I'm trying to combine two SQL statments to find for an item:
1. How many units were sold (in the invoiceline table)
2. How many units were returned (in the creditmemoline table)
select sum(InvoiceLineQuantity) as QSold from InvoiceLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and InvoiceLineItemRefFullName = 'CB:CB001'
select sum(CreditmemoLineQuantity) as CMSold from CreditmemoLine where txnDate <= #11/1/2012# and txnDate >= #8/3/2012# and CreditMemoLineItemRefFullName = 'CB:CB001'
Is it possible to combine these two statments together? The only thing they really have in common is the ItemRefFullName -
This loops through a bunch of items, so I'd rather only make one call the the datbase each time if possible.