Tham khảo tài liệu 'sql puzzles & answers- p5', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 142 PUZZLE 34 CONSULTANT BILLING the hours worked multiplied by the applicable hourly billing rate. For example the sample data shown would give the following answer Results name totalcharges Larry Moe since Larry would have 3 5 hours 25 rate 4 hours 30 rate and Moe 2 hours 15 rate . Answer 1 I think the best way to do this is to build a VIEW then summarize from it. The VIEW will be handy for other reports. This gives you the VIEW CREATE VIEW HourRateRpt emp_id emp_name work_date bill_hrs bill_rate AS SELECT emp_name work_date bill_hrs SELECT bill_rate FROM Billings AS B1 WHERE bill_date SELECT MAX bill_date FROM Billings AS B2 WHERE AND AND FROM HoursWorked AS H1 Consultants AS C1 WHERE Then your report is simply SELECT emp_id emp_name SUM bill_hrs bill_rate AS bill_tot FROM HourRateRpt GROUP BY emp_id emp_name Please purchase PDF Split-Merge on to remove this watermark. PUZZLE 34 CONSULTANT BILLING 143 But since Mr. Buckley wanted it all in one query this would be his requested solution SELECT SUM bill_hrs SELECT bill_rate FROM Billings AS B1 WHERE bill_date SELECT MAX bill_date FROM Billings AS B2 WHERE AND AND FROM HoursWorked AS H1 Consultants AS C1 WHERE GROUP BY This is not an obvious answer for a beginning SQL programmer so let s talk about it. Start with the innermost query which picks the effective date of each employee that immediately occurred before the date of this billing. The next level of nested query uses this date to find the billing rate that was in effect for the employee at that time that is why the outer correlation name B1 is used. Then the billing rate is returned to the expression in the SUM function and multiplied by the number of hours worked. Finally the outermost query .
