难以找到解决方案来计算缺少工作人员的天数

1 回答

• 0

  首先，您的某些日期是“日期”类型，其他日期必须是“日期时间”类型 . 这对于连接非常重要：在比较“date”和“datetime”类型字段时，您可能需要将MySQL DATE（）函数应用于“datetime”字段以启用直接比较 . （我假设您正在使用MySQL：SQL系统之间存在一些小的差异，在日历日期类型，范围和功能中：您还需要警惕日期计算功能结果的范围，因为这些可以有时候有实际限制 . ）以下示例使用子查询：某些版本的MySQL可能比其他版本更好地支持这些 .

  我以前创建过这种类型的查询，我认为基本上有两种方法可以解决这个问题：

  • 创建自业务开始以来所有工作日的查找表;并使用tbl_attendance将此连接（使用WHERE条件：右侧NULL）连接起来 . 这种方法设计简单，但在计算效率方面非常不理想，并且没有必要采用这种方法......

  • 使用tbl_attendance评估员工工作的日历天数 . 通过评估日期范围的总和/差异，评估员工应该工作的日历天数 . 您将使用MySQL函数，如：IF（），DATEDIFF（），DATE（），WEEKDAY（） - 您的雇佣 Contract 形式将决定您的报告应采用何种形式，这将决定您的确切形式 . SQL查询 .

  按日期列出官方公众假期是合理的（政府几乎可以随时创建/更改/取消法定公共假期）;但另一方面，按日期范围列出休假期和出勤期 . 所以我可以看到你的 table /场地设计背后的基本原理（看起来不错）;但是你应该知道你的解决方案（非常正确）具有设计方法1和设计方法2的元素 .

  在设计/测试SQL查询之前，您需要列出一些基本规则，验证规则/查询/触发器将支持这些规则：Make sure that date-ranges of leave-periods and attendance-periods do not, and cannot, overlap with the date-ranges of other leave-periods or attendance-periods.另外，验证/确保end_dates很可能存在错误（错误/遗漏）在我的建议中（以下）;因为我没有测试过这个特定的代码（虽然我已经编写了类似的代码） .

  首先，让我们编写一个显示 total number of days attended 的查询 . 我认为我们也不会假设紧急出勤从未发生在星期六或另一个被排除的日子 . （否则，查询会更复杂一些） . 我建议：

  SELECT staff_id,COUNT(DATE(checked_in)) FROM tbl_attendance GROUP BY staff_id,DATE(checked_in) WHERE checked_in>:start_date AND checked_in<:end_date;
  

  我在这里为你的stand_date，end_date条件使用参数化格式;可以在PHP / PDO中使用如下：

  $pdo=//You need to create a PDO object: see the manual for more information…
  $q=$pdo->prepare(SELECT staff_id,COUNT(DATE(checked_in)) AS attended_start_days FROM tbl_attendance GROUP BY staff_id,DATE(checked_in) WHERE checked_in>=:start_date AND checked_in<:end_date;);
  $q->bindValue(':start_date',$start_date,\PDO::PARAM_STR);
  $q->bindValue(':end_date',$start_date,\PDO::PARAM_STR);
  $q->execute();
  $staff_attended_start_days=$q->fetchAll(\PDO::FETCH_OBJ);
  //etc.
  

  请注意，无论您在SQL中看到“：start_date”还是“：end_date”，这些都是查询的指定日期范围参数 . 无论您在查询中看到“start_date”或“end_date”（无冒号），这些都来自您的数据库字段/记录！

  接下来，我们需要 total number of days official public holiday （假设公众假期不允许在周六或其他被排除的日子时记录在数据库中） - 我在此假设您的日期范围包含start_date，并且不包括end_date（一种不等式格式，使某些计算更简单）：

  SELECT COUNT(holiday_date) FROM tbl_holiday WHERE holiday_date>=:start_date AND holiday_date<end_date;
  

  要计算 total number of days of leave ，我可以（也许应该）使用DATEDIFF;但是由于你有一个名为"no_of_days"的单独的数据库字段，我忘了休假期间也要暂时作弊并且假装假期不能与你查询的时期重叠（这个假设显然是错误的）：

  SELECT staff_id,SUM(no_of_days) FROM tbl_leave GROUP BY staff_id WHERE start_date>=:start_date AND end_date<:end_date;
  

  现在要解决我们刚才做出的明显不正确的假设：我们可能会查看属于指定日期范围的休假期间部分 . 从概念上讲，最简单的方法是使用子查询（我们实际上必须忽略你的字段，"no_of_days"，然后再从头开始计算，为了这些目的，不需要这个字段）：

  SELECT staff_id,SUM(DATEDIFF(bounded_end_date,bounded_start_date))+1 
  FROM (SELECT staff_id,IF(start_date>:start_date,start_date,:start_date) AS bounded_start_date,IF(end_date<:end_date,end_date,:end_date) AS bounded_end_date FROM tbl_leave WHERE end_date>:start_date AND start_date<:end_date) AS qry_bounded_leave 
  GROUP BY staff_id;
  

  1部分是因为如果您从1月5日开始到1月5日结束休假，则此期限为1天（但差异为0天！）休假期限始终比1天长1天 . 开始日期和结束日期之间的差异 .

  WRAPPING IT ALL UP ：

  现在，考虑到上述假设; number of days absent without leave within a specified time-period 等于：

  • 指定时间段内的日历天数

  减去以下的总和：

  • 实际参加的天数（见上文）

  • 指定时间段内的假日天数（见上文）

  • 指定时间段内的休假天数，减去（不包括）这些休假期间的休假天数（如果我的某些假设可能违反，则可能需要进行其他排除） .

  number of calendar-days within a specified time-period 由下式给出：

  SELECT DATEDIFF(:end_date,:start_date)+1 AS days_in_range;
  

  • 您可以在MySQL中执行此操作，但不能在某些其他玩具RDBMS系统中执行此操作，例如MS Access（您必须包含虚拟“FROM”子句） .

  接下来，我们将调整我们对休假期间的查询，以排除假期（这需要子查询中的JOIN）：

  SELECT staff_id,SUM(DATEDIFF(bounded_end_date,bounded_start_date))+1-SUM(holiday_days) AS leave_days_excluding_holidays 
  FROM 
      (SELECT 
          staff_id,
          IF(start_date>:start_date,start_date,:start_date) AS bounded_start_date,
          IF(end_date<:end_date,end_date,:end_date) AS bounded_end_date,
          COUNT(holiday_date) AS holiday_days 
      FROM tbl_leave INNER JOIN tbl_holiday ON (holiday_date>=start_date AND holiday_date>=:start_date AND holiday_date<end_date AND holiday_date<=:end_date) 
      GROUP BY staff_id,start_date,end_date
      WHERE end_date>:start_date AND start_date<:end_date
  ) AS qry_bounded_leave 
  GROUP BY staff_id;
  

  我们将需要更进一步，并在每个查询中包含SQL代码，以考虑星期六和任何其他排除的工作日 . 只要开始日期和结束日期从未真正落在被排除的工作日，这实际上是一个相当简单的计算，其中start_date和end_date之间的周末数由以下内容给出：

  DATEDIFF(end_date,start_date) DIV 7 + IF(WEEKDAY(end_date)<WEEKDAY(start_date),1,0)
  

  "DIV"这里是MySQL integer division operator，向下舍入（如果你使用的是MySQL以外的东西，操作符可能会有所不同） . 你基本上需要仔细阅读我的 Headers 下的详细计算，"WRAPPING IT ALL UP";考虑如何排除星期六和其他任何被排除的工作日的影响 . （我没有时间进一步说明，此时如何做到这一点;但我希望这至少为您的解决方案提供了一个起点！）

  作为最后的警告：我假设所有日期/日期时间都是UTC / GMT / Zulu，以及我假设没有人在夜班工作（假设由于时区问题，你的典型情况考勤开始时间在午夜左右，以UTC / GMT表示...这可能会对您的计算产生影响 . 所以您可能需要更进一步，并放松这个假设：让查询足够聪明以考虑到这一点！）适当地解除这些假设，并使查询足够聪明，以涵盖所有边缘情况;可能有必要更多地了解您的雇佣 Contract ，以及您想要的报告的详细信息：出现/缺席的具体含义是什么？适用什么标准？检查字段上的条件等？ （如果您正在为中小型企业设计这些查询，仅限白天的操作仅跨越彼此非常接近的几个时区，并且您无需准确查询跨越转换的班次期间的工作时间在夏令时;然后你可能会认为UTC / GMT是技术上的过度杀戮，只是为了简单起见在数据库中使用本地时间！）

  回复于 2024-04-20T15:03:03+08:00