我有一个db表的员工时钟数据,我想每天查询。但是,当员工的轮班时间跨越午夜时,查询将错过确定该员工的总工时所需的事务。
ID NUMBER (PK)
EmpId VARCHAR
TransActType VARCHAR
Start TIMESTAMP
Account VARCHAR
1 EmpA ClockIn 7/7/20 8am Account1
2 EmpB ClockIn 7/7/20 9am Account7
3 EmpC ClockIn 7/7/20 9am Account1
4 EmpA Switch 7/7/20 10am Account3
5 EmpA Switch 7/7/20 11am Account6
6 EmpC Switch 7/7/20 1pm Account4
7 EmpC ClockOut 7/7/20 3pm
8 EmpD ClockIn 7/7/20 5pm Account5
9 EmpD Switch 7/7/20 6pm Account6
10 EmpB ClockOut 7/7/20 6pm
11 EmpD Switch 7/7/20 7pm Account4
12 EmpA ClockOut 7/7/20 8pm
13 EmpD Switch 7/8/20 1am Account3
14 EmpD ClockOut 7/8/20 2am
15 EmpA ClockIn 7/8/20 8am Account1
...
我对7/7/20的人工费的要求是
SELECT * FROM labor li where li.start between 7/7/20 12 am and 7/7/20 11:59 pm order by empId, start
只会拿到1-12的记录,但也应该拿到13和14的记录。
我的应用程序代码通过循环查询结果和区分连续员工事务的开始时间来计算每个员工在一天中收取的帐户持续时间。
没有记录13和14,我无法确定员工d的account4和account3持续时间。
也许我的数据库设计有缺陷,因为我只存储事务的开始时间,然后使用应用程序代码逻辑计算持续时间。我决定这样设计,这样如果记录被移动/插入/删除,那么如果结束时间也被持久化,我就不会有太多的机会出现个别持久化的员工人工项重叠。上面的人工数据示例显示,员工可以全天更改帐户,并且每天有多个人工事务。
我希望有一个查询可以在每个员工的基础上提前查看,如果该员工的最后一个事务不是时间戳范围内的“打卡”,则继续抓取记录,直到找到一个为止。
同样地,我不能让那个雇员的第一笔交易是前一天的打卡。
如果这些查询几乎是不可能的,数据库的设计应该改变,那么我想知道。
5条答案
按热度按时间lztngnrs1#
I am hoping for a query that could look ahead in time on a per employee basis and if the last transaction of that employee is not a "ClockOut" for the timestamp range, keep grabbing records until one is found.
```with
dates(d1, d2) as (select date '2020-07-07', date '2020-07-08' from dual),
main as (select id, empid, transacttype, start_, account,
max(transacttype) keep (dense_rank last order by start_)
over (partition by empid) mtt
from li join dates on d1 <= start_ and start_ < d2),
miss as (select empid, max(id) mnid from main where mtt = 'ClockIn' group by empid),
cout as (select empid, min(id) mxid
from li join dates on start_ >= d2 join miss using (empid)
where transacttype = 'ClockOut'
group by empid)
select id, empid, transacttype, start_, account from main union all
select li.id, li.empid, li.transacttype, li.start_, li.account from li
join miss on li.empid = miss.empid and li.id > mnid
join cout on li.empid = cout.empid and li.id < mxid
order by empid, start_
with
dates(d1, d2) as (select date '2020-07-07', date '2020-07-08' from dual),
main as (select id, empid, transacttype, start_, account,
max(transacttype) keep (dense_rank last order by start_)
over (partition by empid) mtt
from li join dates on d1 <= start_ and start_ < d2),
miss as (select empid, max(start_) mnst from main where mtt = 'ClockIn' group by empid),
cout as (select empid, min(start_) mxst
from li join dates on start_ >= d2 join miss using (empid)
where transacttype = 'ClockOut'
group by empid)
select id, empid, transacttype, start_, account from main union all
select li.id, li.empid, li.transacttype, li.start_, li.account from li
join dates on start_ >= d2
join cout on li.empid = cout.empid and li.start_ <= mxst
order by empid, start_
mkh04yzy2#
如果您使用的是最新版本的oracle,则可以使用
match_recognize()
要跟踪时钟输入/切换/时钟输出,然后根据时钟输入时间进行过滤:由于过滤器发生得晚,您可以使用内联视图将其缩小,至少缩小到最小日期/时间:
db<>小提琴
如果你能找到一个合理的范围,你也可以选择一个最大范围,比如说排除一天之后的任何东西:
我的应用程序代码通过循环查询结果和区分连续员工事务的开始时间来计算每个员工在一天中收取的帐户持续时间
如果我知道你在做什么,你也可以在查询中做所有这些:
db<>小提琴
2g32fytz3#
听起来你想抵消这些天,所以从凌晨2点到8点之间的某个时间点开始,不知道具体在哪里,所以我们假设是凌晨5点。
只需减去5小时的日期比较。或者:
或者,以更方便索引的方式:
bpzcxfmw4#
我想这正是你想要的:sqlfiddle
我不确定这是最好的解决方案,但也许你可以用它来构建你的实际解决方案。可能思考stibbons的答案会比这个更好,如果适应从评论中澄清的要求(“所以不,如果当天是7/8/20,我不想接13和14”)。
vuktfyat5#
您可以使用简单的分析函数“first\u value”来表示:
如您所见,它添加了新的列clockout,其中包含下一次clockout的时间,因此您可以轻松地将所需的数据添加到查询中。
完整测试用例:
结果