======  ======



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