我应该如何编写带条件乘法的sql语句? [英] How should I write an sql statement with conditional multiplication?

问题描述

鉴于我有一个类似以下的表格:

Given I have a table like the following:

时间表

+-------+------------+--------+----------------+-------+---------+
| 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中执行此操作?

if I have to calculate the total cost per dept per employee type and per shift, how should I do this in sql ?

考虑到如果包括午夜在内，我的工资将乘以25％吗?

Given that if a shift includes midnight I multiply my wage by 25% ?

我有一个示例输出，如下所示，但不知道如何执行此操作?:

I have a sample output as follows but no clue how to do this ?:

+------------+---------------+----------------+---------+
| 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语句: 但是我应该如何使它工作呢?

This is my sql statement until now: But how should I get this to work ?

 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;

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;

我遇到以下错误，但似乎无法弄清楚为什么?

I am getting the following error but can't seem to figure out why ?

您的SQL语法有错误；检查手册 对应于您的MariaDB服务器版本，以使用正确的语法 在'CASE WHEN HOUR(STR_TO_DATE(sfrom，'％h％p')')附近 STR_TO_DATE(slength，'％H'))> 23然后'在第2行

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CASE WHEN HOUR(STR_TO_DATE(sfrom,'%h%p') + STR_TO_DATE(slength,'%H')) > 23 then ' at line 2

推荐答案

您可以在sql语句中的case子句中使用cost字段.例如:

You can use case clause in your sql statement for the cost field. Like for eg:

case when <<your clause for night shift>> then (slength*10)+(0.25*10*slength)
else (slength*10) end as cost

假设它是每小时10 $.您可以根据您的业务规则设置夜班条件.

Assuming that it is 10$ per hour. You can put night shift condition as per your business rules.

这篇关于我应该如何编写带条件乘法的sql语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！