teratail header banner
teratail header banner
質問するログイン新規登録

回答編集履歴

3

追加

2017/07/03 04:22

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -27,4 +27,19 @@
27
27
  where t1.work_day BETWEEN '2015-06-01' AND '2015-06-31'
28
28
  GROUP BY 1,2
29
29
  ```
30
- 働いていない週が有ると抜けてしまうのが難点です。
30
+ 働いていない週が有ると抜けてしまうのが難点です。
31
+
32
+ これの方が良いかも
33
+ ```sql
34
+ select t4.team_nam,
35
+ DATE_SUB(work_day, INTERVAL (WEEKDAY(work_day)+1) DAY) wday,
36
+ sum(t1.working_hours*t2.pay/8) AS 6月のコスト from time_sheets t1
37
+ join
38
+ daily_salary t2 on t1.member_id=t2.member_id
39
+ join
40
+ team_member t3 on t1.member_id=t3.member_id
41
+ join
42
+ team t4 on t3.team_id=t4.team_id
43
+ where t1.work_day BETWEEN '2015-06-01' AND '2015-06-31'
44
+ GROUP BY 1,2
45
+ ```

2

1週間前にしましたがどうでしょうか

2017/07/03 04:22

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -16,7 +16,7 @@
16
16
  さらに週にしてみました(日曜日始め)
17
17
  ```sql
18
18
  select t4.team_nam,
19
- DATE_ADD('2015-01-01',INTERVAL (WEEKOFYEAR(work_day)*7-(WEEKDAY('2015-01-01')+1)) DAY) wday,
19
+ DATE_ADD('2015-01-01',INTERVAL ((WEEKOFYEAR(work_day)-1)*7-(WEEKDAY('2015-01-01')+1)) DAY) wday,
20
20
  sum(t1.working_hours*t2.pay/8) AS 6月のコスト from time_sheets t1
21
21
  join
22
22
  daily_salary t2 on t1.member_id=t2.member_id

1

修正

2017/07/03 02:20

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -11,4 +11,20 @@
11
11
  team t4 on t3.team_id=t4.team_id
12
12
  where t1.work_day BETWEEN '2015-06-01' AND '2015-06-30'
13
13
  GROUP BY t4.team_id
14
- ```
14
+ ```
15
+
16
+ さらに週にしてみました(日曜日始め)
17
+ ```sql
18
+ select t4.team_nam,
19
+ DATE_ADD('2015-01-01',INTERVAL (WEEKOFYEAR(work_day)*7-(WEEKDAY('2015-01-01')+1)) DAY) wday,
20
+ sum(t1.working_hours*t2.pay/8) AS 6月のコスト from time_sheets t1
21
+ join
22
+ daily_salary t2 on t1.member_id=t2.member_id
23
+ join
24
+ team_member t3 on t1.member_id=t3.member_id
25
+ join
26
+ team t4 on t3.team_id=t4.team_id
27
+ where t1.work_day BETWEEN '2015-06-01' AND '2015-06-31'
28
+ GROUP BY 1,2
29
+ ```
30
+ 働いていない週が有ると抜けてしまうのが難点です。