sql - 检查前一天是否存在现有记录
问题描述
我正在尝试检查在所选计划日期的前一天是否有现有记录。我们如何做到这一点？我尝试使用LAG()，但在整理数据时遇到了问题，因为NULL.
DATEDIFF()也不会解决我的问题，因为我需要检查该日期是否存在现有数据，而不是 -1 日期。
例如，我想查询什么。2020-01-02前一天有记录和记录，必要时应反映NULL。有没有办法做到这一点？
这将是结果。
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-01NULL
这是第一张桌子。
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
此查询正在处理此问题。
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
这是查询结果。
for t1.recordin, 和t2.recordout, scheduleate 分别来自t0.scheduledate与表上对应的日期。
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。
我需要实现的是一个新的列，它会像这样显示。
类似的预期结果。
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
您的帮助将不胜感激。谢谢你。
解决方案
根据您的样本数据，我假设以下内容：
- 每天最多记录每个徽章。
recordin并recordout在同一天。checktype无关紧要- 您知道如何在原始表中生成数据。
如果是这样，您可以使用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;
如果以上不正确，我建议您提出一个新问题。尝试简化问题。您相当复杂的查询与您提出的问题无关，因此对问题没有帮助。
推荐阅读
- php - PHP SQLSRV 驱动程序将 NULL 作为空字符串获取
- aws-iot - 对于从 AWS IoT greengrass lambda 到 AWS IoT Core 规则引擎的通信，是否应将 lambda 配置为设备（事物）？
- javascript - node.js / javascript 应用程序是否支持 AES 密文窃取？
- discord - Discord py - 在“on_raw_reaction_add”函数中创建某个类别的频道？
- vb.net - 如何解析 XML 而不会对文件中的注释抛出异常
- javascript - 为什么我只在 Javascript 计算器中的减号运算符中看到 NaN？
- css - 如何降低标签的高度 - 棱角材料
- c++ - 如何旋转 AVFrame？
- typescript - 单击事件返回未定义。为什么？
- c# - SignalR Core 和接收消息