Explanation of SQL
This query provides a breakdown of the ins and outs for the Direct Cost from Bills GL Account.
Risk of Data Corruption if Run Improperly
None. This is a selection query and no data is modified in the running of it.
SQL
SELECT *, --(select SUM(amount) -- from GL -- where -- GLAccountID = 15 -- and EntryDateTime >= Temp.EntryDateTime -- and ID >= GLID) as RunningBalance, SUM(amount) OVER ( Partition BY TransHeaderID ) AS TransHeaderBalance, SUM(amount) OVER ( Partition BY TransPartID ) AS TransPartBalance, SUM(amount) OVER ( Partition BY IsUserEntered ) AS UserEnteredBalance, SUM(amount) OVER ( Partition BY 1 ) AS TotalBalance FROM ( SELECT GL.ID AS GLID, EntryDateTime, Amount, CASE TransDetailClassTypeID WHEN 11100 THEN 'Bill' WHEN 10100 THEN 'Order' ELSE NULL END AS SOURCE, CASE TransDetailClassTypeID WHEN 11100 THEN (SELECT BillNumber FROM TransHeader WHERE ID = GL.TransactionID) WHEN 10100 THEN (SELECT OrderNumber FROM TransHeader WHERE ID = GL.TransactionID) ELSE NULL END AS TransHeaderNumber, CASE TransDetailClassTypeID WHEN 11100 THEN (SELECT LineItemNumber FROM VendorTransDetail WHERE ID = GL.TransDetailID) WHEN 10100 THEN (SELECT LineItemNumber FROM TransDetail WHERE ID = GL.TransDetailID) ELSE NULL END AS LineItem, CASE WHEN EntryType=1 THEN 1 ELSE 0 END IsUserEntered, CASE WHEN (TransPartID IS NULL AND TransDetailClassTypeID = 11100) THEN (SELECT AttachedOrderPartID FROM VendorTransDetail WHERE ID = TransDetailID) ELSE TransPartID END AS TransPartID, PartID, (SELECT ItemName FROM Part WHERE ID = PartID) AS Part, TransDetailID, TransDetailClassTypeID, TransactionID AS TransHeaderID FROM GL WHERE GLAccountID = 15 -- Direct Cost from Bills Account ) Temp ORDER BY EntryDateTime, GLID
Version Information
- Entered : 5/2012
- Version : 4.6+
You could leave a comment if you were logged in.