AcumaticaSQLQueriesAndFunctions/RankedActivities.sql

IF OBJECT_ID ('dbo.RankedActivities','V') IS NOT NULL DROP VIEW dbo.RankedActivities
GO 
CREATE VIEW dbo.RankedActivities
AS
SELECT 
        CR.CompanyID,
        PM.ProjectID,
        c.ContractCD,
        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
    join dbo.Contract c on C.CompanyID = PM.CompanyID and C.ContractID = PM.ProjectID
       WHERE CR.CompletedDate is not NULL 
        and CR.[Type] = 'COMP'