回答編集履歴
3
変更
test
CHANGED
|
@@ -140,7 +140,7 @@
|
|
|
140
140
|
|
|
141
141
|
team.name AS team_name,
|
|
142
142
|
|
|
143
|
-
round(COALESCE(sum(t2.working_hours * daily_salary.pay / 8),0) / sum(t1.working_hours * daily_salary.pay / 8),2) AS pcost
|
|
143
|
+
round(COALESCE(sum(t2.working_hours * daily_salary.pay / 8),0) / sum(t1.working_hours * daily_salary.pay / 8) * 100,2) AS pcost
|
|
144
144
|
|
|
145
145
|
FROM
|
|
146
146
|
|
2
変更
test
CHANGED
|
File without changes
|
1
追加
test
CHANGED
|
@@ -127,3 +127,69 @@
|
|
|
127
127
|
;
|
|
128
128
|
|
|
129
129
|
```
|
|
130
|
+
|
|
131
|
+
|
|
132
|
+
|
|
133
|
+
SUBクエリしないのも作ってみました
|
|
134
|
+
|
|
135
|
+
```sql
|
|
136
|
+
|
|
137
|
+
SELECT
|
|
138
|
+
|
|
139
|
+
DATE_SUB(t1.work_day, INTERVAL (WEEKDAY(t1.work_day) + 1) DAY) AS sweek,
|
|
140
|
+
|
|
141
|
+
team.name AS team_name,
|
|
142
|
+
|
|
143
|
+
round(COALESCE(sum(t2.working_hours * daily_salary.pay / 8),0) / sum(t1.working_hours * daily_salary.pay / 8),2) AS pcost
|
|
144
|
+
|
|
145
|
+
FROM
|
|
146
|
+
|
|
147
|
+
time_sheets t1
|
|
148
|
+
|
|
149
|
+
INNER JOIN
|
|
150
|
+
|
|
151
|
+
daily_salary
|
|
152
|
+
|
|
153
|
+
ON
|
|
154
|
+
|
|
155
|
+
t1.member_id = daily_salary.member_id
|
|
156
|
+
|
|
157
|
+
INNER JOIN
|
|
158
|
+
|
|
159
|
+
team_member
|
|
160
|
+
|
|
161
|
+
ON
|
|
162
|
+
|
|
163
|
+
t1.member_id = team_member.member_id
|
|
164
|
+
|
|
165
|
+
INNER JOIN
|
|
166
|
+
|
|
167
|
+
team
|
|
168
|
+
|
|
169
|
+
ON
|
|
170
|
+
|
|
171
|
+
team_member.team_id = team.team_id
|
|
172
|
+
|
|
173
|
+
LEFT JOIN
|
|
174
|
+
|
|
175
|
+
time_sheets t2 ON DATE_SUB(t1.work_day, INTERVAL (WEEKDAY(t1.work_day) + 1 + 7) DAY)=DATE_SUB(t2.work_day, INTERVAL (WEEKDAY(t2.work_day) + 1) DAY)
|
|
176
|
+
|
|
177
|
+
AND t1.member_id=t2.member_id
|
|
178
|
+
|
|
179
|
+
WHERE
|
|
180
|
+
|
|
181
|
+
t1.work_day
|
|
182
|
+
|
|
183
|
+
BETWEEN
|
|
184
|
+
|
|
185
|
+
'2015-06-01'
|
|
186
|
+
|
|
187
|
+
AND
|
|
188
|
+
|
|
189
|
+
'2015-06-30'
|
|
190
|
+
|
|
191
|
+
GROUP BY
|
|
192
|
+
|
|
193
|
+
sweek,team.team_id
|
|
194
|
+
|
|
195
|
+
```
|