DROP FUNCTION IF EXISTS dbo.delete_me
GO

CREATE FUNCTION dbo.delete_me(
    @agent_id int,
    @latest_job_date datetime
)
RETURNS TABLE AS
RETURN (
    WITH Per_Agent_Latest_Scores_CTE AS (
        SELECT TOP 30
            g.AccuracyScore AS AccuracyScore,
            g.FormattingScore AS FormattingScore
        FROM dbo.AgentJobs j
        INNER JOIN dbo.TcAgentJobGrades g ON g.JobId = j.Id
        LEFT JOIN dbo.AgentJobs gj ON g.GradingJobId = gj.Id
        WHERE j.AgentId = @agent_id AND j.CompletedOn < @latest_job_date
            AND j.CancelledOn IS NULL AND j.Type = 20
            AND gj.CancelledOn IS NULL -- exclude cancelled grades
        ORDER BY j.CompletedOn DESC
    )
    SELECT
        ROUND(AVG(CAST(AccuracyScore AS DECIMAL) / 20), 2) AS AvgAccuracy,
        ROUND(AVG(CAST(FormattingScore AS DECIMAL) / 20), 2) AS AvgFormatting
    FROM
        Per_Agent_Latest_Scores_CTE
)
GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT
    j.CompletedOn,
    j.AgentLevelOnClaim,
    p.ProjectNumber,
    g.AccuracyScore / 20 as ReceivedAccuracyScore,
    g.FormattingScore / 20 as ReceivedFormattingScore,
    f.Rating as CustomerRating,
    computedScores.AvgAccuracy AS CurrentAccuracyScore,
    computedScores.AvgFormatting AS CurrentFormattingScore
FROM dbo.AgentJobs j
INNER JOIN dbo.Projects p on p.Id = j.ProjectId
CROSS APPLY dbo.delete_me(j.AgentId, j.CompletedOn) computedScores
LEFT JOIN dbo.TcAgentJobGrades g on g.JobId = j.Id
LEFT JOIN dbo.AgentJobs gj ON g.GradingJobId = gj.Id
LEFT JOIN dbo.TcCustomerDocumentFeedback f on f.AgentJobId = j.Id
WHERE j.CompletedOn > '2017-01-01' and j.CompletedOn < '2017-05-01'
    AND j.CancelledOn IS NULL AND j.Type = 20
    AND gj.CancelledOn IS NULL -- exclude cancelled grades
ORDER BY j.CompletedOn

GO

DROP FUNCTION dbo.delete_me
GO