AcumaticaSQLQueriesAndFunctions/RankedActiveTasks.sql

IF OBJECT_ID ('dbo.RankedActiveTasks','V') IS NOT NULL DROP VIEW dbo.RankedActiveTasks
GO 
CREATE VIEW dbo.RankedActiveTasks
as
WITH RankedActiveTasks AS (
    SELECT
        CompanyID,
        ProjectID,
        TaskID,
        TaskCD,
        -- row number 1 = newest active task for that project
        ROW_NUMBER() OVER (
            PARTITION BY CompanyID, ProjectID
            ORDER BY CreatedDateTime DESC      -- ← change if you need a different definition of “most recent”
        ) AS rn
    FROM dbo.PMTask
    WHERE
        Status = 'A'           
)
SELECT
    CompanyID,
    ProjectID,
    TaskID,
    TaskCD
FROM RankedActiveTasks
WHERE rn = 1;      -- keep only the newest active task per project