Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_direct_cost_from_bills_details [2019/01/27 11:28] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== 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 =====
 +
 +
 +
 +<code 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
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 5/2012
 +  * Version : 4.6+
 +
 +
 +