AcumaticaSQLQueriesAndFunctions/ReleasedTranGroupsOffset.sql

IF OBJECT_ID ('dbo.ReleasedTranGroupsOffset','V') IS NOT NULL DROP VIEW dbo.ReleasedTranGroupsOffset
GO 
CREATE VIEW dbo.ReleasedTranGroupsOffset
AS
SELECT 
    ROW_NUMBER() OVER (
        ORDER BY 
            p.CompanyID, 
            p.ProjectID, 
            p.OffsetAccountGroupID 
    ) AS RowKey,
    p.CompanyID, 
    p.ProjectID, 
    p.OffsetAccountGroupID, 
    COUNT(*) AS ResultsSummed,
    SUM(p.Amount) AS [Amount], 
    SUM(p.TranCuryAmount) AS [TranCuryAmount], 
    SUM(p.ProjectCuryAmount) AS [ProjectCuryAmount], 
    SUM(p.ProjectCuryInvoicedAmount) AS [ProjectCuryInvoicedAmount],
    SUM(p.InvoicedAmount) AS [InvoicedAmount]
FROM PMTran p 
WHERE p.Released = 1
GROUP BY 
    p.CompanyID, 
    p.ProjectID, 
   p.OffsetAccountGroupID