SELECT FV FieldValueString AS Appraiser FV ValueId FV2 FieldValueInt N

 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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
SELECT FV.FieldValueString AS Appraiser, FV.ValueId, FV2.FieldValueInt, S.Name,
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,
SUM(
CASE Approved
WHEN 1 THEN A.[Mileage $]
WHEN 0 THEN 0
END
) AS ApprovedAmount
FROM sm_forms_FieldValues AS FV
JOIN sm_forms_sptable_networkclaimssolutions_AssignmetsMileageRequests AS MR
ON ((MR.Appraiser = FV.ValueId) AND
FV.InstanceId=2778 AND FV.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)
JOIN sm_forms_FieldValues AS FV2
ON ((FV2.ValueId = FV.ValueId) AND
FV2.InstanceId=2778 AND FV2.FieldId=
(
SELECT Id
FROM sm_forms_Fields
WHERE InstanceId=2778 AND Title='Subcontractor'
) )
LEFT OUTER JOIN sm_forms_sptable_networkclaimsnorth_Subcontractors AS S
ON (FV2.FieldValueInt = S.ValueId)
WHERE MR.Date >= '05/01/2008' AND MR.Date <= '09/07/2008 23:59:59.999'
GROUP BY FV.ValueId, FV.FieldValueString, FV2.FieldValueInt, S.Name
ORDER BY S.Name, FV.FieldValueString