namespace PERMDAL.QueryBuilders;

// Required index: TAPPRAISAL(EMPLOYEEID, TENANTID, STATUS)
public static class TrendQB
{
    public const string GET_TREND = @"
SELECT TOP (@LastNCycles)
    ap.NAME                    AS CycleName,
    ap.PERFORMANCETO           AS PerformanceTo,
    a.FINALPERCENTAGE          AS FinalPercentage,
    ISNULL(rs.GRADELETTER, '') AS GradeLetter,
    a.PERCENTINCREASE          AS PercentIncrease
FROM   TAPPRAISAL a
JOIN   MAPPRAISALPLAN ap ON ap.APPRAISALPLANID = a.APPRAISALPLANID
                        AND ap.TENANTID        = a.TENANTID
LEFT JOIN MRATINGSCALE rs ON a.FINALPERCENTAGE BETWEEN rs.SLABFROM AND rs.SLABTO
                          AND rs.TENANTID      = a.TENANTID
                          AND rs.STATUS        = 1
WHERE  a.EMPLOYEEID = @EmployeeId
AND    a.TENANTID   = @TenantId
AND    a.STATUS     = 4
ORDER  BY ap.PERFORMANCETO DESC";
}