如何汇总两个日期之间的注册小时数(不仅仅是两个日期之间的总小时数)

Question

我找不到与我匹配的问题。如果您知道一个，请告诉我。

我

需要一个选择语句，向我显示员工在日期之间的总缺勤时间。

例：

员工 Dennis 于 2014-06-10 至 2014-06-13 缺席。该员工在日期之间的缺勤时间不同。假设丹尼斯一直这样缺席：

2014-06-10: 477 minutes
2014-06-11: 498 minutes
2014-06-12: 491 minutes
2014-06-13: 397 minutes
total absence: 1863 minutes

我想得到这样的输出：

我有表格
员工时间 每天有缺勤时间。
缺勤日期具有缺勤的开始日期和结束日期。

EmployeeTime看起来像这样：

| EmployeeName | Date | RegisteredHour |

缺席日看起来像这样：

| FromDate | TillDate | AbsenceId | KeyNo |

我有第三个表LogTable，可以通过KeyNo和EmployeeId将这两个表链接在一起。

我得到的输出：

| EmployeeName | FromDate   | TillDate   | Days |Days without weekends | Date       | RegisteredHour | Total min absence
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-10 | 477            | 9540
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-11 | 498            | 9960
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-12 | 491            | 9960
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 2014-06-13 | 397            | 7940
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-09 | 420            | 2100
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-10 | 0              | 0
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-11 | 0              | 0
| Joanne       | 2014-05-09 | 2014-05-12 | 4    |        2             | 2014-05-12 | 450            | 2250

我需要得到这个结果：

| EmployeeName | FromDate   | TillDate   | Days |Days without weekends | Total min absence
| Dennis       | 2014-06-10 | 2014-06-13 | 4    |        4             | 1863
| Joanne       | 2014-06-09 | 2014-06-12 | 4    |        2             | 870

我的代码：

SELECT DISTINCT 
    EmployeeTime.Name,
    CONVERT(VARCHAR(10),FromDate,20) FromDate,
    CONVERT(VARCHAR(10),Tilldate,20) Tilldate,
    (DATEDIFF(DD, FromDate, Tilldate) +1) TotalDays,
    (DATEDIFF(DD, FromDate, Tilldate) +1) - (DATEDIFF(wk, FromDate, Tilldate) * 2) DaysMinusWeekends,
    CONVERT(VARCHAR(10),Dato,105) DateDato,
    RegisteredHour,
    SUM(RegisteredHour) Total
FROM EmployeeTime, AbsenceDays, LogTable
WHERE EmployeeTime.EmployeeId = LogTable.EmployeeId
    AND LogTable.KeyNo = AbsenceDays.KeyNo
    AND EmployeeTime.EmployeeId = 'D994'
    AND (DATEDIFF(DD, FromDate, TillDate) +1) = 4
    AND CONVERT(VARCHAR(10),FromDate,20) <= CONVERT(VARCHAR(10),Dato,20)
    AND CONVERT(VARCHAR(10),TillDate,20) >= CONVERT(VARCHAR(10),Dato,20)
    GROUP BY EmployeeTime.EmployeeId,FromDate, TillDate, Dato, RegisteredHour
    ORDER BY EmployeeTime.EmployeeId

注意：我需要一个答案，您不自连接表或使用 WITH 查询;SELECT 必须是 SQL 中的第一件事，否则程序将无法理解它。

Answer 1

这是我

对答案的最佳猜测。 它可能不会按原样为您工作，因为：（1） 我不知道您所有表的完整表架构;（2）我不明白你说的"我需要一个你不连接表的答案"是什么意思;（3）我只知道PostgreSQL，不了解SQL Server。

在英语中，您要做的是选择一行AbsenceDays，然后使用FromDate和TillDate在EmployeeTime中选择一组行，然后将这些行中的RegisteredHour列求和，而不是绝对日期差异。 最明显的方法是

SELECT et.EmployeeName, ad.FromDate, ad.TillDate, SUM(et.RegisteredHour)
  FROM LogTable lt
  JOIN EmployeeTime et ON lt.EmployeeId = et.EmployeeId
  JOIN AbsenceDays ad  ON lt.KeyNo      = ad.KeyNo
 WHERE et.Date >= ad.FromDate
   AND et.Date <= ad.TillDate
-- AND et.EmployeeName = 'Dennis'
 GROUP BY et.EmployeeName, ad.FromDate, ad.TillDate
 ORDER BY et.EmployeeName, ad.FromDate;

回顾一下你的代码，似乎你基本上嵌入了这个，但混淆了很多对我来说似乎不必要的转换和日期差异。 这可能是因为，同样，我只知道PostgreSQL。 我建议你像这样将代码精简到最低限度，然后重新添加转换，直到数据库接受它。

另一个潜在的问题是，如果（EmployeeName、FromDate、TillDate）不能唯一地标识一个连续缺勤。 （您在 AbsenceDays 中似乎有两个 ID 列;这表明这可能是一个问题。 你说的分钟和小时也不一致，所以我不知道你真正想要哪个。 最后，我不知道如何计算非周末天数。