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+
 +
 +
 +