The following select gives me the right data using DISTINCT, but then how do I get a sum of it, which is what I need?

select distinct billtopayduedate, billtopayrefnumber,bill.vendorreffullname, billtopayamountdue, assetaccountreffullname from bill nosync, billtopay nosync, billitemline nosync, item nosync where billtopay.billtopaytxnid = bill.txnid and bill.ispaid=false and bill.txnid = billitemline.txnid and BillItemLine.ItemLineItemRefListID = Item.ListID and assetaccountreffullname like '%inventory%'

Since OpenAmount isn't available in Bill, I am trying to look at BillToPay, join over to billitemline/item to get the G/L account so I can find the open A/P balance for any bill where all/part of the bill is to inventory.  (In my case, if one line item is to inventory they all will be.)

I tried to follow your other instructions about getting to OpenBalance, but couldn't do so expediently.

To find the sum of the A/P OpenBalance  I can do just:

sp_report VendorBalanceSummary show Amount_1 as APOpenBalance
parameters DateMacro = 'All', SummarizeColumnsBy = 'TotalOnly' where Label = 'TOTAL'

Thanks.

I should have qualified my question to ask how you do it without an SP_Report.  These take too long for my purpose.  I'm writing a spreadsheet model and need to not wait on it to run reports.  Fast nosync queries only.  So far working great.

I ended up returning the recordset and looping through it.

But is there a way to do it with regular SQL?

In SQL we do sum(column) and group by, for example:

SELECT Employee.PayrollInfoClassRefFullName, sum(Transaction.Amount) as Amount 
FROM Transaction, Employee
where Transaction.EntityRefListID=Employee.ListID
and Transaction.TxnType='Paycheck'
and (Transaction.TxnDate >= {d'2007-01-01'} and Transaction.TxnDate < {d'2008-01-01'})
Group by Employee.PayrollInfoClassRefFullName

This gives you the sum for each Employee.PayrollInfoClassRefFullName, whereas I am looking for one sum for the whole list, hence the typical use of the COMPUTE statement.

I want my SELECT statement to return one number: a sum of all the records in "Bill to Pay" where one or more corresponding line items in expenseline or itemline is for inventory.

I ended up writing a program to loop through each BillToPay record, then finding the corresponding records in expenseline and itemline (union all via txnID) then looping through those to see if any are inventory, thus classifying each billtopay record.  This is because I can't find the openbalance any other fast way.  This worked, but takes too long, so I'm re-writing with a single query and .GETROWS, then looping through the array and that seems to be much faster, but a lot more coding.

I was looking for a more elegant way to do this with a single/fast nosync SQL select.

If you can show me how to do that, great.  If no way to do the above with QODBC SQL statements, no need to reply.

To total all the BillToPay amounts you simply do:

Select sum(BillToPayAmountDue) as OpenAPBalance from BillToPay

To total all the BillToPay amounts involving stock (inventory) only and not expenses you simply do:

Select sum(BillToPayAmountDue) as OpenInvtBalance from BillToPay, BillItemLine
where BillToPay.BillToPayTxnID = BillItemLine.TxnID
and BillItemLine.ItemLineSeqNo = 1

OK, this is great information I would not have found on my own.

What does ItemLineSeqNo do?  Where can I find a reference on what the codes of this field mean?

Check for bills related to stock is only one of my queries.

The next is to determine if either a related expenseline or itemline contains an accountreffullname like "Freight%" or "Handling%" or "Customers%" -- and get a sum of all those bills.  If even one lineitem or expense item matches on these keywords, then I want to include this bill in my total.  I want the SQL statement to return one sum total.

I've created a combined set of expenseline and itemline where ispaid = false with "UNION ALL".  To get the account for itemline, I have to join the item table.  But, I can't seem to join the BillToPay table to the union because I can't include sub-Selects in FROM statements.

Is there an easier way?

I looked at this again.

LineItemSeqNo is just the line number.  I'm not merely looking to see if an billitem exits, but what G/L account was used for it.

I need to look at the actual account being charged by the line item, and I'm trying to do a sum/select on that.

My criteria on billitemline is to join the Item table and see if assetaccountreffullname is not null.  Then I know it's related to inventory.

My second criteria is to look at salesorPurchaseAccountRefFullName if it's an item, or the ExpenseLineAccountRefFullName if it's in bilexpensitem to determine if certain accounts exist for that billtopay.  If yes, then that billtopay is in my sum. 

In other words, I am looping through billtopay.  For each billtopay record, I am looking for the corresponding records in billexpenseline or billitemline+Item to determine if certain accounts pertain to that bill -- if so I include it in my sum, otherwise not.  I've accomplished this in code, but would like to do it in a single (fast nosync) query, if it can be done.  I'm calling the query in a common function, so it has to be faster than an SP_Report.  (I have found that returning a recordset to an array with GETROWS and looking through it is very fast, but a lot more code.)

(The reason I am using billtopay is because that  table tells me the open amount due.  Neither bill or billitemline or billexpenseline tell me that without a lot of complication -- as you mentioned in your other post.  If I look backwards from the detail tables to billtopay, I sometime double count because a billtopay record could have a mixture of related accounts in the detail, so I can't do it that way.)

Well all I got to say is that the whole of AP in QuickBooks is badly constructed when compared to their AR side.  I would be more inclined to use the Transaction table instead of BillExpenseLine, BillItemLine and Item tables like this:

SELECT sum(BillToPay.BillToPayAmountDue) as OpenACCBalance FROM BillToPay, Transaction
where BillToPay.BillToPayTxnID = Transaction.TxnID
and (Transaction.AccountRefFullName = 'Computers'
or Transaction.AccountRefFullName = 'Freight & Delivery')

I understand and appreciate the good/bad of Qbooks data model, no problem there.

Before building my own transaction table with UNION ALL statements, I was going to use the Transaction table.  But then, in another post, #2777, you wrote:

"Actually there's no such thing as the transaction table in QuickBooks, we have simply renamed an audit report. This also means that if a transaction is modified yesterday, today and tomorrow you will get three instances of the one transaction. "

So, wouldn't the SQL you suggested potentially give me ambiguous results (not as to amount because that is in BillToPay), but if the account was changed, I would get the old/new both?  Or would I filter somehow for the most recent? 

I would like to use the transaction table as you suggested as that would save a lot of time/effort in constructing my own view.