Explanation of SQL
This SQL statement will pull a list of all records grouped by Order # in the GL database that have a balance in ARs.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
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
Version Information
- Entered : 6/9/2010
You could leave a comment if you were logged in.