SELECT FV FieldValueString AS Appraiser FV ValueId SUM CASE Approved W

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
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