SELECT FV.FieldValueString AS Appraiser, FV.ValueId,
SUM (
CASE Approved
WHEN 1 THEN MR.Miles
WHEN 0 THEN 0
END
) AS ApprovedMiles,
SUM(
CASE Approved
WHEN 1 THEN 0
WHEN 0 THEN MR.Miles
END
) AS NotAprovedMiles,
SUM (
CASE Approved
WHEN 1 THEN 1
WHEN 0 THEN 0
END
) AS ApprovedRequest,
SUM(
CASE Approved
WHEN 1 THEN 0
WHEN 0 THEN 1
END
) AS NotAprovedRequest
FROM sm_forms_FieldValues AS FV
JOIN sm_forms_sptable_networkclaimssolutions_AssignmetsMileageRequests AS MR
ON ((MR.Appraiser = FV.ValueId) AND
InstanceId=2778 AND FieldId=
(
SELECT Id
FROM sm_forms_Fields
WHERE InstanceId=2778 AND Title='Name'
) )
JOIN sm_forms_sptable_networkclaimssolutions_Assignments AS A
ON (A.ValueId = MR.Assignment)
WHERE MR.Date >= '05/01/2008' AND MR.Date <= '09/07/2008 23:59:59.999'
GROUP BY FV.ValueId, FV.FieldValueString
ORDER BY FV.FieldValueString