AcumaticaSQLQueriesAndFunctions/ReleasedAccountGroups.sql

--[mssql:Native]
IF OBJECT_ID ('dbo.ReleasedAccountGroups','V') IS NOT NULL DROP VIEW dbo.ReleasedAccountGroups
GO 
CREATE VIEW dbo.ReleasedAccountGroups
AS
SELECT 
    ROW_NUMBER() OVER (
        ORDER BY 
            p.CompanyID, 
            p.ProjectID, 
            p.AccountGroupID
    ) AS RowKey,
    p.CompanyID, 
    p.ProjectID, 
    p.AccountGroupID, 
    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.AccountGroupID