鉴于我有一个类似以下的表格:
日程
+-------+------------+--------+----------------+-------+---------+ | index | date | empid | dept | sfrom | slength | +-------+------------+--------+----------------+-------+---------+ | 0 | 09-24-2018 | 943023 | RADIOLOGY | 03PM | 8 | | 1 | 09-24-2018 | 891046 | SURGERY | 07AM | 8 | | 2 | 09-24-2018 | 878397 | RADIOLOGY | 11PM | 8 | | 3 | 09-24-2018 | 886190 | CARDIOLOGY | 03PM | 8 | | 4 | 09-24-2018 | 878397 | RADIOLOGY | 11PM | 8 | | 5 | 09-24-2018 | 891330 | SURGERY | 03PM | 8 | | 6 | 09-24-2018 | 995561 | SURGERY | 11PM | 8 | | 7 | 09-24-2018 | 967577 | MATERNITY | 03PM | 8 | | 8 | 09-24-2018 | 913891 | PEDIATRICS | 03PM | 8 | | 9 | 09-24-2018 | 939148 | EMERGENCY | 07AM | 8 | | 10 | 09-24-2018 | 995636 | RADIOLOGY | 11PM | 8 | | 11 | 09-24-2018 | 995561 | EMERGENCY | 11PM | 8 | | 12 | 09-24-2018 | 967577 | SURGERY | 07AM | 8 | | 13 | 09-24-2018 | 883069 | INTENSIVE CARE | 07AM | 8 | | 14 | 09-24-2018 | 951876 | ONCOLOGY | 03PM | 8 | | 15 | 09-24-2018 | 884866 | CARDIOLOGY | 07AM | 8 | | 16 | 09-24-2018 | 864425 | ONCOLOGY | 03PM | 8 |
员工表
+-------+--------+------------+-----------+---------+----------------+----------------+------+ | index | empid | lastName | firstName | emptype | cellphone | homephone | ftpt | +-------+--------+------------+-----------+---------+----------------+----------------+------+ | 0 | 919675 | Cermak | Vern | LPN | (138)7198-0862 | (655)2249-9926 | FT | | 1 | 906704 | Paille | Lorilee | NULL | (858)1100-2722 | (377)1506-1986 | PT | | 2 | 900486 | Ober | Shalanda | NULL | (210)1508-8132 | (820)1612-0197 | FT | | 3 | 883367 | Clyburn | Jeffry | LPN | (420)7798-7220 | (476)7661-3070 | FT | | 4 | 953807 | Quarles | Timmy | LPN | (513)2756-9892
如果我必须计算每个员工类型和每个班次的每个部门的总成本,该如何在sql中执行此操作?
假设如果包括午夜在内,我的工资将乘以25%?
我有以下示例输出,但不知道如何执行此操作?:
+------------+---------------+----------------+---------+ | DEPARTMENT | EMPLOYEE_TYPE | SHIFT | COST | +------------+---------------+----------------+---------+ | Anesthesia | LPN | 7 AM 鈥�3 | $24,567 | | Anesthesia | LPN | 3 PM 鈥�11PM | $18,546 | | Anesthesia | LPN | 11 PM 鈥�7AM | $22,874 | | Anesthesia | NA | 7 AM 鈥�3 PM | $9,764 | | Anesthesia | NA | 3 PM 鈥�11 PM | $10,287 | | Anesthesia | NA | 11 PM 鈥�7 AM | $6, 875 | | Anesthesia | RN | 7 AM 鈥�3 PM | $33,123 | +------------+---------------+----------------+---------+
到目前为止,这是我的sql语句:但是,如何使它起作用?编辑1:
Select t1.dept, t2.emptype, t1.sfrom, t1.slength, SUM(t1.slength) as hours CASE WHEN CAST(STR_TO_DATE(sfrom,'%h%p') + STR_TO_DATE(slength,'%H')) > STR_TO_DATE(23,'%H') then (slength*10*1.25) else (slength*10) end as cost from schedule_2 as t1 Join (select empid, emptype from employees_2) as t2 ON t1.empid=t2.empid GROUP BY dept, emptype, slength;
编辑2:
Select t1.dept, t2.emptype, t1.sfrom, t1.slength, SUM(t1.slength) as hours CASE WHEN HOUR(STR_TO_DATE(sfrom,'%h%p') + STR_TO_DATE(slength,'%H')) > 23 then (slength*10*1.25) else (slength*10) from schedule_2 as t1 Join (select empid, emptype from` employees_2) as t2 ON t1.empid=t2.empid GROUP BY dept, emptype, slength;
我收到以下错误,但似乎无法找出原因?
您的SQL语法有误;检查与您的MariaDB服务器版本相对应的手册以获取正确的语法,以在’CASE WHEN HOUR(STR_TO_DATE(sfrom,’%h%p’)+ STR_TO_DATE(slength,’%H’))> 23然后在行附近使用2个
您可以在sql语句中的case子句中使用cost字段。例如:
case when <<your clause for night shift>> then (slength*10)+(0.25*10*slength) else (slength*10) end as cost
假设它是每小时10 $。您可以根据您的业务规则设置夜班条件。