我有两张桌子,一张是简略的日历:

create table dbo.calendar
(
    [date] datetime
)

insert into dbo.calendar values ('20150101 00:00:00 AM') -- 1/1/15
insert into dbo.calendar values ('20150102 00:00:00 AM') -- 1/2/15
insert into dbo.calendar values ('20150103 00:00:00 AM') -- 1/3/15
insert into dbo.calendar values ('20150104 00:00:00 AM') -- 1/4/15

另一个是时钟
create table dbo.timeclock
(
    id integer,
    punchtime datetime,
    punchtype varchar(25)
)

--employee 1
insert into dbo.timeclock values (1,'20150102 08:00:00 AM','in') -- 8am  1/2/15
insert into dbo.timeclock values (1,'20150102 05:00:00 PM','out') -- 5pm  1/2/15
insert into dbo.timeclock values (1,'20150103 08:00:00 AM','in') -- 8am  1/3/15
insert into dbo.timeclock values (1,'20150103 05:00:00 PM','out') -- 5pm  1/3/15

--employee 2
insert into dbo.timeclock values (2,'20150103 08:00:00 AM','in') -- 8am  1/3/15
insert into dbo.timeclock values (2,'20150103 05:00:00 PM','out') -- 5pm  1/3/15

我想要的是在dbo.timeclock中为每个员工在日历中的每个日期都没有记录的每一天创建一个“未输入时间”记录。结束表如下所示:
id      punchtime               type
--------------------------------------------
1       20150101 00:00:00 AM    no time entered
1       20150102 08:00:00 AM    in
1       20150102 05:00:00 PM    out
1       20150103 08:00:00 AM    in
1       20150103 05:00:00 PM    out
1       20150104 00:00:00 AM    no time entered
2       20150101 00:00:00 AM    no time entered
2       20150102 00:00:00 AM    no time entered
2       20150103 08:00:00 AM    in
2       20150103 05:00:00 PM    out
2       20150104 00:00:00 AM    no time entered

我可以用光标来做,但它运行得太慢了,我知道这不是正确的方法。
谢谢你的帮忙!

最佳答案

创建一个包含范围@all dates中所有可能日期的表
然后将丢失的记录插入如下内容:
查询

INSERT INTO dbo.timeclock
SELECT id
    ,d.punchtime
    ,'no time entered'
FROM (
    SELECT DATE
        ,id
    FROM @alldates d
    CROSS JOIN (
        SELECT DISTINCT id
        FROM dbo.timeclock
        ) ids
    ) userDateCross
LEFT JOIN dbo.timeclock ON timeclock.id = userdatecross.id
    AND userdatecross.DATE = timeclock.DATE
WHERE timeclock.punctype IS NULL

关于mysql - 如何根据有限的日历为每个员工创建缺少的日期记录?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/30987383/

10-11 08:00