This SQL statement will pull a list of all records grouped by Order # in the GL database that have a balance in ARs.

None. This is a selection query and no data is modified in the running of it.

DEFINE EndDate 4/30/2010 8:32:37 PM
SELECT SUM(Amount) AS ARAmt, OrderID FROM "GL Database"
WHERE AccountCode = 1200 AND DateTime < '[EndDate]'
GROUP BY OrderID
HAVING SUM(amount) > 0.005 OR SUM(amount) < -0.005
;tmp1
SELECT 
OrderID
,CompanyName
,STATUS AS Current_Order_Status
,PickedUpDate AS InvoiceDate
,VoidDate
,SalesSubtotal
,TaxTotal
,SalesTotal
,TotalPayments
,BalanceOutstanding
,'[EndDate]' AS AR_EndDate
,ARAmt
FROM "TMP1" 
LEFT JOIN "Order Database" O ON TMP1.OrderID = O.OrderID
LEFT JOIN "Customer Database" C ON C.CustomerID = O.CustomerID
ORDER BY O.PickedUpDate
  • Entered : 6/9/2010
You could leave a comment if you were logged in.