获取Postgres中日期序列在特定范围内的员工的出勤情况

Question

我有一张考勤表，上面有employee_id、日期和打卡时间。

Emp_Id     PunchTime
101      10/10/2016 07:15
101      10/10/2016 12:20
101      10/10/2016 12:50
101      10/10/2016 16:31
102      10/10/2016 07:15

这里我只有工作日的日期。我想要得到一系列给定日期段的员工的出勤列表。我也需要这一天。结果应如下所示

date        |  day     |employee_id |   Intime          |     outtime       |
2016-10-09  |  sunday  |   101      |                   |                   |
2016-10-10  |  monday  |   101      | 2016-10-10 7:15AM |2016-10-10 4:31 PM |

Answer 1

您可以生成一个日期列表，然后对它们执行外连接：

下面显示了10月份的所有日期：

select d.date, a.emp_id, 
       min(punchtime) as intime, 
       max(punchtime) as outtime
from generate_series(date '2016-10-01', date '2016-11-01' - 1, interval '1' day) as d (date)
  left join attendance a on d.date = a.punchtime::date
group by d.date, a.emp_id;
order by d.date, a.emp_id;

因为您需要每天的第一个和最后一个时间戳，所以这可以使用一个简单的group by查询来完成。

然而，这不会在non_existing的日子里重复emp_id。

Answer 2

类似下面的内容将生成日期范围的列表(开始和结束的日期范围在您的punchtime表中找到)，每个日期都有员工和intime、outtime。在这里检查SQL字段：

http://sqlfiddle.com/#!15/d93bd/1

WITH RECURSIVE minmax AS
(
    SELECT MIN(CAST(time AS DATE)) AS min, MAX(CAST(time as DATE)) AS max
    FROM emp_time
),
dates AS
(
    SELECT m.min as datepart
    FROM minmax m
    RIGHT JOIN emp_time e ON m.min = CAST(e.time as DATE)
    UNION ALL
    SELECT d.datepart + 1 FROM dates d, minmax mm 
    WHERE d.datepart + 1 <= mm.max
)
SELECT d.datepart as date, e.emp, MIN(e.time) as intime, MAX(e.time) as outtime FROM dates d
LEFT JOIN emp_time e ON d.datepart = CAST(e.time as DATE)
GROUP BY d.datepart, e.emp
ORDER BY d.datepart;