/
employee-activity.txt
71 lines (61 loc) · 1.41 KB
/
employee-activity.txt
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
61
62
63
64
65
66
67
68
69
70
71
SELECT employee,COUNT(*) as count FROM partner_team_month_score GROUP BY employee ORDER BY count DESC;
while($data = mysql_fetch_assoc($result)){
$row[] = $data;
}
print_r($row);
CREATE VIEW partner_activity
AS
SELECT
RC.partner,
RC.time,
CASE WHEN RC.result = "pass" THEN 4 ELSE -6 END AS score,
MONTH(RC.time) AS month,
YEAR(RC.time) AS year
FROM rotationchecks RC
UNION
SELECT
UC.partner,
UC.time,
CASE WHEN UC.result = "pass" THEN 1 ELSE -2 END AS score,
MONTH(UC.time) AS month,
YEAR(UC.time) AS year
FROM uniformchecks UC
UNION
SELECT
OB.partner,
OB.time,
CASE WHEN OB.result = "pass" THEN 5 ELSE -5 END AS score,
MONTH(OB.time) AS month,
YEAR(OB.time) AS year
FROM observations OB
UNION
SELECT
BC.partner,
BC.time,
CASE WHEN BC.result = "pass" THEN 2 ELSE -2 END AS score,
MONTH(BC.time) AS month,
YEAR(BC.time) AS year
FROM bagchecks BC
UNION
SELECT
QU.partner,
QU.time,
CASE WHEN QU.result = "pass" THEN 2 ELSE 0 END AS score,
MONTH(QU.time) AS month,
YEAR(QU.time) AS year
FROM questionchecks QU
CREATE VIEW partner_team_month_score
AS
SELECT P.employee,
P.team,
PS.time,
PS.score,
MONTH(PS.time) AS month,
YEAR(PS.time) AS year
FROM partners P
LEFT OUTER JOIN partner_score PS ON PS.partner = P.employee
;
SELECT employee, Team, SUM(score) FROM partner_team_month_score
WHERE MONTH = 6 AND YEAR = 2018 AND team='blue'
GROUP BY employee, team
ORDER BY 3 DESC LIMIT 5