检查前一天是否有现有记录 [英] Check if there's an existing record on the day before
问题描述
我正在尝试检查所选计划日期前一天是否有现有记录.我们如何做到这一点?我尝试使用 LAG()，但是由于 NULL，我在整理数据时遇到了问题.
I'm trying to check if there's an existing record on the day before the selected schedule date. How do we do this? I tried using LAG(), but I'm having problem when it comes to sorting out the data because of the NULL.
DATEDIFF() 也不能解决我的问题，因为我需要检查该日期是否有现有数据，而不是 -1 日期.
DATEDIFF() won't solve my problem as well because I need to check if there's an existing data on that date, and not -1 the date.
如果示例，我想查询什么.2020-01-02的前一天有recordin和recordout，必要时应反映NULL.有没有办法做到这一点?
What I want to query if example. Day before 2020-01-02 has recordin and recordout, it should reflect NULL if necessary. Is there a way to do this?
这就是结果.
scheduledate schedulein scheduleout recordin recordout prevrecordin prevrecordout
2020-01-02 08:00:00.0000000 17:00:00.0000000 07:41:12.0000000 17:16:54.0000000 NULL NULL
prevrecordin 和 prevrecordout 都是 NULL 因为 2020-01-01 是 NULL.
prevrecordin and prevrecordout will be both NULL since 2020-01-01 is NULL.
这是第一张桌子.
badgenumber checktype recordin checkdate
10 I 2019-12-20 07:35:58.000 2019-12-20
10 I 2019-12-21 05:18:14.000 2019-12-21
10 I 2019-12-23 07:35:33.000 2019-12-23
10 I 2019-12-26 07:48:20.000 2019-12-26
10 I 2019-12-27 07:41:03.000 2019-12-27
10 I 2019-12-28 07:35:42.000 2019-12-28
10 I 2020-01-02 07:41:12.000 2020-01-02
10 I 2020-01-03 07:50:12.000 2020-01-03
10 I 2020-01-04 07:41:12.000 2020-01-04
此查询正在由此处理.
.....
OUTER APPLY (
SELECT TOP(1) t1.recordin, t1.badgenumber
FROM (SELECT MAX(userinfo.badgenumber) AS badgenumber, MAX(RTRIM(checkinout.checktype)) AS 'checktype',
MIN(checkinout.checktime) as 'recordin', MIN(CONVERT(date,checkinout.checktime)) as checkdate,
MAX(RTRIM(employeemasterfile.employeeidno)) AS 'employeeidno' FROM ((checkinout INNER JOIN userinfo
ON checkinout.userid = userinfo.userid) INNER JOIN employeemasterfile ON userinfo.badgenumber = employeemasterfile.fingerscanno)
INNER JOIN departmentmasterfile ON LEFT(employeemasterfile.employeeidno, 4) = LEFT(departmentmasterfile.departmentcode, 4)
WHERE CONVERT(date,checkinout.checktime) BETWEEN DATEADD(DAY, -1,'2019-12-21') AND DATEADD(DAY, 1,'2020-01-05') and badgenumber = '10'
AND CHECKINOUT.CHECKTYPE = 'I' COLLATE SQL_Latin1_General_CP1_CS_AS GROUP BY userinfo.badgenumber, LEFT(checkinout.checktime,14)) AS t1
WHERE
t1.recordin BETWEEN DATEADD(HOUR,-(t0.noofhoursduty),t0.mergetimeinorig) AND DATEADD(HOUR, (t0.noofhoursduty),t0.mergetimeinorig)
AND t1.badgenumber = t0.fingerscanno
AND t0.schedulename !='REST'
ORDER BY abs(datediff(minute, t0.mergetimeinorig, t1.recordin )) DESC
) t1
这是第二张桌子.
badgenumber checktype recordout checkdate
10 O 2019-12-20 20:41:46.000 2019-12-20
10 O 2019-12-21 14:12:34.000 2019-12-21
10 O 2019-12-23 17:03:44.000 2019-12-23
10 O 2019-12-26 17:05:16.000 2019-12-26
10 O 2019-12-27 17:02:32.000 2019-12-27
10 O 2019-12-28 17:07:38.000 2019-12-28
10 O 2020-01-02 17:16:54.000 2020-01-02
10 O 2020-01-03 17:05:11.000 2020-01-03
10 O 2020-01-04 17:04:42.000 2020-01-04
此查询正在处理此内容.
This is being processed by this query.
OUTER APPLY (
SELECT TOP(1) t2.recordout, t2.badgenumber
FROM (SELECT MAX(userinfo.badgenumber) AS badgenumber, MAX(RTRIM(checkinout.checktype)) AS 'checktype',
MAX(checkinout.checktime) as 'recordout', MAX(CONVERT(date,checkinout.checktime)) as checkdate,
MAX(RTRIM(employeemasterfile.employeeidno)) AS 'employeeidno' FROM ((checkinout INNER JOIN userinfo
ON checkinout.userid = userinfo.userid) INNER JOIN employeemasterfile ON userinfo.badgenumber = employeemasterfile.fingerscanno)
INNER JOIN departmentmasterfile ON LEFT(employeemasterfile.employeeidno, 4) = LEFT(departmentmasterfile.departmentcode, 4)
WHERE CONVERT(date,checkinout.checktime) BETWEEN DATEADD(DAY, -1,'2019-12-21') AND DATEADD(DAY, 1,'2020-01-05') and badgenumber = '10'
AND CHECKINOUT.CHECKTYPE = 'O' COLLATE SQL_Latin1_General_CP1_CS_AS GROUP BY userinfo.badgenumber, LEFT(checkinout.checktime,14)) AS t2
WHERE
t2.recordout BETWEEN DATEADD(HOUR,-(t0.noofhoursduty),t0.mergetimeoutorig) AND DATEADD(HOUR, (t0.noofhoursduty),t0.mergetimeoutorig)
AND t2.badgenumber = t0.fingerscanno
AND t0.schedulename !='REST'
ORDER BY abs(datediff(minute, t0.mergetimeoutorig, t2.recordout )) DESC
) t2
这是查询结果.
对于t1.recordin 和t2.recordout，scheduledate 分别来自t0.scheduledate 和表上对应的日期.
for t1.recordin, and t2.recordout, scheduledate comes from t0.scheduledate with the corresponding date on the table respectively.
scheduledate schedulein scheduleout recordin recordout
2019-12-21 06:00:00.0000000 14:00:00.0000000 05:18:14.0000000 14:12:34.0000000
2019-12-23 08:00:00.0000000 17:00:00.0000000 07:35:33.0000000 17:03:44.0000000
2019-12-24 08:00:00.0000000 17:00:00.0000000 NULL NULL
2019-12-25 08:00:00.0000000 17:00:00.0000000 NULL NULL
2019-12-26 08:00:00.0000000 17:00:00.0000000 07:48:20.0000000 17:05:16.0000000
2019-12-27 08:00:00.0000000 17:00:00.0000000 07:41:03.0000000 17:02:32.0000000
2019-12-28 08:00:00.0000000 17:00:00.0000000 07:35:42.0000000 17:07:38.0000000
2019-12-30 08:00:00.0000000 17:00:00.0000000 NULL NULL
2019-12-31 08:00:00.0000000 17:00:00.0000000 NULL NULL
2020-01-01 08:00:00.0000000 17:00:00.0000000 NULL NULL
2020-01-02 08:00:00.0000000 17:00:00.0000000 07:41:12.0000000 17:16:54.0000000
2020-01-03 08:00:00.0000000 17:00:00.0000000 07:50:12.0000000 17:05:11.0000000
2020-01-04 08:00:00.0000000 17:00:00.0000000 07:41:12.0000000 17:04:42.0000000
我也尝试过 CASE WHEN (LAG())，不幸的是，由于 NULL 值，我也无法做到.
I tried doing CASE WHEN (LAG()) as well, unfortunately I am unable to do it because of the NULL value as well.
我需要实现的是一个新的列，其显示方式有点像这样.
What I need to achieve is a new column that will display somewhat like this.
类似的预期结果.
scheduledate schedulein scheduleout recordin recordout prevrecordin prevrecordout
21/12/2019 06:00:00 14:00:00 05:18:14 14:12:34 NULL NULL
23/12/2019 08:00:00 17:00:00 07:35:33 17:03:44 05:18:14 14:12:34
24/12/2019 08:00:00 17:00:00 NULL NULL 07:35:33 17:03:44
25/12/2019 08:00:00 17:00:00 NULL NULL NULL NULL
26/12/2019 08:00:00 17:00:00 07:48:20 17:05:16 NULL NULL
27/12/2019 08:00:00 17:00:00 07:41:03 17:02:32 07:48:20 17:05:16
28/12/2019 08:00:00 17:00:00 07:35:42 17:07:38 07:41:03 17:02:32
30/12/2019 08:00:00 17:00:00 NULL NULL 07:35:42 17:07:38
31/12/2019 08:00:00 17:00:00 NULL NULL NULL NULL
01/01/2020 08:00:00 17:00:00 NULL NULL NULL NULL
02/01/2020 08:00:00 17:00:00 07:41:12 17:16:54 NULL NULL
03/01/2020 08:00:00 17:00:00 07:50:12 17:05:11 07:41:12 17:16:54
04/01/2020 08:00:00 17:00:00 07:41:12 17:04:42 07:50:12 17:05:11
非常感谢您的帮助.谢谢.
Your help would be greatly appreciated. Thank you.
推荐答案
根据您的示例数据，我假设如下:
Based on your sample data, I am assuming the following:
- 每个徽章每天最多记录.
recordin和recordout在同一天.checktype无关- 您知道如何在原始表中生成数据.
- At most record per badge per day.
recordinandrecordouton the same day.checktypeis irrelevant- You know how to generate the data in the original table.
如果是这样，你可以使用lag():
If so, you can use lag():
select t.*,
(case when datediff(day,
lag(recordin) over (partition by badgenumber order by recordin),
recordin
) <> 1
then null
else lag(recordin) over (partition by badgenumber order by recordin)
end),
(case when datediff(day,
lag(recordin) over (partition by badgenumber order by recordin),
recordin
) <> 1
then null
else lag(recordout) over (partition by badgenumber order by recordin)
end),
from t;
如果上述情况不正确，我建议您提出一个新问题.尝试简化问题.您相当复杂的查询与您提出的问题无关，因此对问题没有帮助.
If the above are not true, I would suggest that you ask a new question. Try to simplify the problem. Your rather complex query has nothing to do with the question you are asking, so it doesn't help the question.
这篇关于检查前一天是否有现有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
