AcumaticaSQLQueriesAndFunctions/FirstCompleteTask.sql

IF OBJECT_ID ('dbo.FirstCompleteTask','V') IS NOT NULL DROP VIEW dbo.FirstCompleteTask
GO 
CREATE VIEW dbo.FirstCompleteTask
AS
WITH RankedActivities AS (
    SELECT 
        CR.CompanyID,
        PM.ProjectID,
        CR.NoteID,
        PM.RefNoteID,
        CR.[Type],
        CR.UIStatus,
        PM.ApprovalStatus,
        CR.CompletedDate,
        CR.Subject,
        CR.Body,
        ROW_NUMBER() OVER (PARTITION BY CR.CompanyID,PM.ProjectID ORDER BY CR.CompletedDate) AS rn
    FROM 
        dbo.CRActivity CR
    JOIN 
        dbo.PMTimeActivity PM 
    ON 
        CR.NoteID = PM.RefNoteID 
        AND CR.CompanyID = PM.CompanyID
        and CR.CompletedDate is not NULL 
        and CR.[Type] = 'COMP'
)
SELECT 
    CompanyID,
    ProjectID,
    NoteID,
    RefNoteID,
    [Type],
    UIStatus,
    ApprovalStatus,
    CompletedDate,
    Subject,
    Body
FROM 
    RankedActivities
WHERE 
    rn = 1