Puzzule34 - SQLパズル演習

問題

各仕事に対するコンサルタントの名前と請求金額を求めたい。請求金額は書く労働時間と時給をかけると求められるが、コンサルタントの時給は期間によって変化する。
　Consultants

 emp_id |  emp_name
--------+------------
      1 | Larry
      2 | Moe
      3 | Curly

Billings

 emp_id | bill_date  | bill_rate
--------+------------+-----------
      1 | 1990-01-01 |     25.00
      2 | 1989-01-01 |     15.00
      3 | 1989-01-01 |     20.00     
      1 | 1991-01-01 |     30.00
 
hoursworked
  job_id | emp_id | work_date  | bill_hrs
 --------+--------+------------+--------------------------------
       4 |      1 | 1990-07-01 |     3.00
       4 |      1 | 1990-08-01 |     5.00
       4 |      2 | 1990-07-01 |     2.00
       4 |      1 | 1991-07-01 |     4.00

Consultants：コンサルタントテーブル Emp_id：社員ID　　emp_name:社員名
Billings:請求書テーブル　　bill_date:請求日　　　　bill_rate:時給
Hoursworked:実働時間テーブル　job_id:実働ID　work_date:実働日　bill_hrs:実働時間

chiakiの解答

考え方

• １つ目のビューで、実働日が請求日より後にある場合の、社員名・請求日・時給・実働日・実働時間を求める。
• ２つ目のビューで、前に作ったビューを社員名と実働日でグループ化し、社員名・請求日の最大値・実働日を求める。
• 最後に、上の二つのビューを使って、給料合計を求める。

SQL文

①１つ目のビュー
CREATE VIEW Consultants2 (emp_id, emp_name, bill_date, bill_rate, bill_hrs, work_date)
AS SELECT C.emp_id, C.emp_name, B.bill_date, B.bill_rate, H.bill_hrs, H.work_date
FROM Consultants C, Billings B, HoursWorked H
WHERE H.emp_id = C.emp_id AND H.emp_id = B.emp_id AND H.work_date >= B.bill_date;

 emp_id |  emp_name  | bill_date  | bill_rate | bill_hrs | work_date
--------+------------+------------+-----------+----------+------------
      1 | Larry      | 1990-01-01 |     25.00 |     3.00 | 1990-07-01
      1 | Larry      | 1990-01-01 |     25.00 |     5.00 | 1990-08-01
      1 | Larry      | 1990-01-01 |     25.00 |     4.00 | 1991-07-01
      1 | Larry      | 1991-01-01 |     30.00 |     4.00 | 1991-07-01
      2 | Moe        | 1989-01-01 |     15.00 |     2.00 | 1990-07-01

②２つ目のビュー
CREATE VIEW Consultants3 (name, bd, wd)
AS SELECT C2.emp_name AS NAME, max(C2.bill_date) AS BD, C2.work_date AS WD
FROM Consultants2 C2
GROUP BY C2.emp_name, C2.work_date;

    name    |     bd     |     wd
------------+------------+------------
 Larry      | 1990-01-01 | 1990-07-01
 Larry      | 1990-01-01 | 1990-08-01
 Larry      | 1991-01-01 | 1991-07-01
 Moe        | 1989-01-01 | 1990-07-01

SELECT C2.emp_name, SUM(C2.bill_rate*C2.bill_hrs)
FROM Consultants2 C2, Consultants3 C3
WHERE C2.emp_name = C3.name AND C2.bill_date = C3.bd AND C2.work_date = C3.wd
GROUP BY C2.emp_name;

結果

  emp_name  |   sum
------------+----------
 Larry      | 320.0000
 Moe        |  30.0000
(2 行)

satomiの解答

考え方

• 副問い合わせで、実働日以前の直近の請求日を選ぶ
• emp_nameでグループ化し、該当する請求日の時給と、実働時間をかけた合計を求める。

SQL文

select c.emp_name,sum(h.bill_hrs*b.bill_rate)
from consultants c,billings b,hoursworked h
where c.emp_id=b.emp_id
and c.emp_id=h.emp_id
and b.bill_date=(select max(b1.bill_date)
from billings b1,hoursworked h1
　　　　　　　　where h.work_date>b1.bill_date
and b1.emp_id=h1.emp_id
　　　　　　　　and h1.emp_id=h.emp_id
　　　　　　　　group by h1.emp_id)
group by c.emp_name;

結果

 emp_name  |   sum
 ------------+----------------------
  Moe      |  30.0000
 Larry     | 320.0000