mysql - SELECT 中的 SELECT？单一报告中的不同数据

Question

我的目标是生成一份报告，为我提供要提交给薪资公司的数据。

我们的发薪期是 1 号到 15 号，然后是 16 号到月底，我可以轻松生成一份报告，显示员工在这些给定日期之间工作了多少小时。

我的问题是加类。加类时间是根据员工在给定一周内的工作时间来计算的。我们一周的开始和结束日期是周日至周六。这是挑战的具体示例。

截至 2016 年 1 月 31 日的发薪期属于第 3、4、5 和 6 周。第 4 和第 5 周有 7 天，第 3 和第 6 周只有 1 天。我想支付(在这个特定工资周期内)员工在第 3、4 和 5 周工作的所有加类费。一旦该周结束，我将在下一个工资周期中向他们支付第 6 周的加类费。

我需要一点帮助。具体一个方向开始寻找。在这种情况下，我会在 SELECT 中嵌入 SELECT 吗？由于我在同一数据源上进行两种不同的旋转操作...我有点困惑。

有人有过这方面的经验吗？想法？

Report for Pay Period Ending 2016-01-31

Employee                 Hours   OT Hr
----------------------- ------- -------
Joe Employee             95.00    1.00
   - Week 3 (1 Day)       7.00    1.00
   - Week 4 (7 Days)     40.00    0.00
   - Week 5 (7 Days)     40.00    0.00
   - Week 6 (1 Day)       8.00    0.00

此示例报告显示，一名员工在截至 2016 年 1 月 31 日的工资期内工作了 16 天，工作时间为 96 小时。第 3 周的 1 小时加类和 7 小时的正常工作时间表明，他们在上一个工资周期中加类，导致第 3 周的总工作时间超过 40 小时。

业务规则:每周工作时间超过 40 小时的员工支付加类费。周被定义为从星期日到星期六的时间。付款在发薪日期进行。发薪日定义为每月 15 日和最后一天。如果付款期发生在一周中间。员工按照上述规定在发薪日前后支付加类费。

Sample Data as Requested

Employee        Start               End               Week  PayPeriod  Duration
John Employee   2016-01-02 09:23:42 2016-01-02 15:13:43 1   1/15/2016   5.83
John Employee   2016-01-04 09:42:30 2016-01-04 17:58:19 2   1/15/2016   8.26
John Employee   2016-01-05 09:46:04 2016-01-05 13:30:03 2   1/15/2016   3.73
John Employee   2016-01-05 14:03:02 2016-01-05 18:06:34 2   1/15/2016   4.06
John Employee   2016-01-06 10:30:43 2016-01-06 17:14:18 2   1/15/2016   6.73
John Employee   2016-01-07 10:05:22 2016-01-07 13:43:59 2   1/15/2016   3.64
John Employee   2016-01-07 14:14:20 2016-01-07 18:05:50 2   1/15/2016   3.86
John Employee   2016-01-08 09:55:59 2016-01-08 17:47:58 2   1/15/2016   7.87
John Employee   2016-01-11 10:28:22 2016-01-11 17:54:04 3   1/15/2016   7.43
John Employee   2016-01-12 09:33:30 2016-01-12 10:08:43 3   1/15/2016   0.59
John Employee   2016-01-12 10:39:59 2016-01-12 18:29:24 3   1/15/2016   7.82
John Employee   2016-01-13 10:41:16 2016-01-13 13:39:29 3   1/15/2016   2.97
John Employee   2016-01-13 13:39:29 2016-01-13 15:05:05 3   1/15/2016   1.43
John Employee   2016-01-13 15:05:06 2016-01-13 17:25:30 3   1/15/2016   2.34
John Employee   2016-01-14 10:32:28 2016-01-14 14:01:33 3   1/15/2016   3.48
John Employee   2016-01-14 14:20:47 2016-01-14 18:07:42 3   1/15/2016   3.78
John Employee   2016-01-15 09:40:31 2016-01-15 17:19:34 3   1/15/2016   7.65
John Employee   2016-01-16 09:40:31 2016-01-16 17:19:34 3   1/31/2016   7.65
John Employee   2016-01-18 10:01:39 2016-01-18 15:40:43 4   1/31/2016   5.65
John Employee   2016-01-18 15:53:38 2016-01-18 18:38:27 4   1/31/2016   2.75
John Employee   2016-01-19 10:43:24 2016-01-19 18:13:04 4   1/31/2016   7.49
John Employee   2016-01-20 10:38:38 2016-01-20 14:16:09 4   1/31/2016   3.63
John Employee   2016-01-20 14:16:09 2016-01-20 17:55:07 4   1/31/2016   3.65
John Employee   2016-01-21 10:39:31 2016-01-21 18:56:42 4   1/31/2016   8.29
John Employee   2016-01-22 10:57:55 2016-01-22 15:44:03 4   1/31/2016   4.77
John Employee   2016-01-22 15:57:54 2016-01-22 18:11:28 4   1/31/2016   2.23
John Employee   2016-01-25 10:08:57 2016-01-25 19:14:21 5   1/31/2016   9.09
John Employee   2016-01-26 10:45:35 2016-01-26 14:17:13 5   1/31/2016   3.53
John Employee   2016-01-26 14:40:51 2016-01-26 18:31:56 5   1/31/2016   3.85
John Employee   2016-01-27 09:53:33 2016-01-27 18:05:40 5   1/31/2016   8.20
John Employee   2016-01-28 10:36:57 2016-01-28 16:28:16 5   1/31/2016   5.86
John Employee   2016-01-28 16:43:20 2016-01-28 19:42:17 5   1/31/2016   2.98
John Employee   2016-01-31 10:00:40 2016-01-31 16:27:46 6   1/31/2016   6.45
John Employee   2016-02-01 10:45:42 2016-02-01 14:04:03 6   2/15/2016   3.31
John Employee   2016-02-01 14:15:06 2016-02-01 17:45:05 6   2/15/2016   3.50
John Employee   2016-02-01 17:45:05 2016-02-01 19:01:34 6   2/15/2016   1.27
John Employee   2016-02-02 11:03:49 2016-02-02 17:40:21 6   2/15/2016   6.61
John Employee   2016-02-03 11:08:06 2016-02-03 17:15:38 6   2/15/2016   6.13
John Employee   2016-02-04 11:20:59 2016-02-04 17:27:15 6   2/15/2016   6.10
John Employee   2016-02-04 17:27:15 2016-02-04 20:19:34 6   2/15/2016   2.87
John Employee   2016-02-05 10:47:57 2016-02-05 17:53:54 6   2/15/2016   7.10
John Employee   2016-02-08 10:51:45 2016-02-08 15:15:28 7   2/15/2016   4.40
John Employee   2016-02-08 15:34:52 2016-02-08 17:30:54 7   2/15/2016   1.93
John Employee   2016-02-09 11:01:09 2016-02-09 13:11:02 7   2/15/2016   2.16
John Employee   2016-02-09 13:11:02 2016-02-09 17:38:03 7   2/15/2016   4.45
John Employee   2016-02-09 17:38:03 2016-02-09 18:34:20 7   2/15/2016   0.94
John Employee   2016-02-10 10:43:39 2016-02-10 11:25:38 7   2/15/2016   0.70
John Employee   2016-02-10 11:25:38 2016-02-10 17:58:11 7   2/15/2016   6.54
John Employee   2016-02-11 10:16:30 2016-02-11 14:06:35 7   2/15/2016   3.83
John Employee   2016-02-11 14:30:17 2016-02-11 17:25:23 7   2/15/2016   2.92
John Employee   2016-02-12 10:46:50 2016-02-12 17:46:38 7   2/15/2016   7.00

我需要支付 2016-01-15 工资期 5.15 小时的加类费，因为他直到 2015-01-15 工资期结束后才超过 40 小时。

如果员工在 2016 年 1 月 15 日工资期结束前工作超过 40 小时，我将需要在 2016 年 1 月 15 日和 2016 年 01 月 31 日工资期支付第三周的加类费.

Answer 1

这是我对加类计算的想法:

1. 您将开始检查该期间的开始日期，是哪一天？如果不是周日，那么提前的日期需要支付一些加类费吗？嗯，要几天？您始终从该周的星期日开始计算该周的加类费。这里一些有用的 mySql 函数是:DAYOFWEEK() 和 DATE_ADD()

2. 然后您将继续，直到到达/超过该期间的结束日期，并且您将计算每周(星期日至星期六)的加类时间。如果星期六超过了工资核算期的结束日期，您将跳过该周进行加类计算。