导出数据计算

Question

我希望使用派生数据来获得根据处理的约会计算的总薪资(每个20英镑)，基本工资是1000英镑。此外，工资应按月和年排序。

任用表有约会日期和时间，员工表有WorkerID和WorkerName，工资有salaryID、任用人员和约会费用(20英镑)。我想按每月1000英镑的基本工资和其他费用(指定的* 20)按日期计算工资总额。我对按日期订购有问题。

预约表

AppointmenID |AppointmentDate  | MechanicID
:----- | -----:     | :----:
AI1   | 2017-01-01  | MI1
AI2   | 2017-01-15  | MI2
AI3   | 2017-02-01  | MI1

薪金表

SalaryID | Salary  | AppointmentCost
:----- | -----:     | :----:
1      | 1000       | 20

预约表

AppointmenID |AppointmentDate  | MechanicID
:----- | -----:     | :----:
AI1   | 2017-01-01  | MI1
AI2   | 2017-01-15  | MI2
AI3   | 2017-02-01  | MI1
AI4   | 2017-01-25  | MI2

机械工作台

MechanicID |MechanicName| SalaryID
:----- | -----:     | :----:
MI1   | Rooney      | 1
MI2   | Stephan     | 1
MI3   | Ronaldo     | 1

预期产出

1月份薪金

AppointmentDate  | MechanicID | MechanicName | Salary| AppointmentCost| Appointment Handled | Salary
:----- |      -----: | :----:   | ----:| :----:|:--|:-----|
2017-01-01  | MI1    | Rooney   | 1000   | 20    |1  | 1020
2017-01-15  | MI2    | Stephan  | 1000   | 20    |2  | 1040

2月份薪金

AppointmentDate  | MechanicID | MechanicName | Salary| AppointmentCost| Appointment Handled | Salary
:-----      | -----:     | :----:  |:----- | -----:| :----:  |:-----
2017-01-01  | MI1  |      Rooney   | MI1   | 1000  | 20      |1      | 1020

有人能帮我吗。

Answer 1

您需要Join's和下面的表达式来计算机械师的工资

salary + ( count of appointmenthandled * appointmentcost )

就像这样

SELECT A.appointmentdate, 
       M.mechanicid, 
       M.mechanicname, 
       S.salary, 
       S.appointmentcost, 
       A.appointmenthandled, 
       salary = S.salary + ( A.appointmenthandled * S.appointmentcost ) 
FROM   mechanic M 
       INNER JOIN salary S 
               ON M.salaryid = S.salaryid 
       INNER JOIN (SELECT mechanicid, 
                          AppointmentHandled = Count(*), 
                          AppointmentDate =Min(appointmentdate) 
                   FROM   appointment A 
                   --Where month(AppointmentDate) = 1 
                   GROUP  BY mechanicid, 
                             Month(appointmentdate)) A 
               ON M.mechanicid = A.mechanicid