我想通过更新查询来存储从查询到出勤表的差异。到目前为止,我有一个表格,DateTime的日期和时间是用户登机手续的日期和时间,我正在计算"然后使用"现在" getDate()的"然后dateTime"的差异。时间格式为24小时而不是12小时。
AttendanceId UserId AttendanceStatus Hours CheckIn/Out DateTime
---------------------------------------------------------------------
1 22 Present 0 In 2016-6-23 5:30:00
2 23 Present 0 In 2016-6-23 5:30:00
3 24 Present 0 In 2016-6-23 5:30:00
4 25 Present 0 In 2016-6-23 5:30:00
我使用的查询
Select [DateTime],
DATEDIFF(MI, (SELECT IIF(ISDATE([DateTime]) = 1, CONVERT(DATETIME, [DateTime], 111), [DateTime]) as DT_Attendance), GETDATE())/60.0 as Hours_Difference
from
Attendance where CheckInCheckOut = 'In'
and [DateTime] = CONVERT(date, getdate())
这是我获得的输出:
Datetime Hours_Difference
---------------------------------
2016-6-23 1.20233434
2016-6-23 1.20233434
现在,在SQL Server中,我想更新表Attendance
并将值'4'存储在列Hours
的所有行中,其中Hours_Difference = 4 or >4
分别在此处(假设)差异,例如:
Datetime Hours_Difference
----------------------------------------
2016-6-23 5:30:00 4.00000000
2016-6-23 5:30:00 5.50323134
表的最终结果应该看起来像:
AttendanceId UserId AttendanceStatus Hours CheckIn/Out DateTime
---------------------------------------------------------------------
1 22 Present 4 In 2016-6-23 5:30:00
2 23 Present 4 In 2016-6-23 5:30:00
3 24 Present 4 In 2016-6-23 5:30:00
4 25 Present 4 In 2016-6-23 5:30:00
从聊天中的讨论中,这个问题似乎是
如果[DateTime]列中的值为4小时或更长时间,则如何将[出勤]表中的[小时]列更新为4
这很复杂,正如肖恩·兰格(Sean Lange)指出的那样,[DateTime]被存储为字符串而不是DateTime数据类型。
这需要OP脚本中的ISDATE
功能,但最终OP找到了他们喜欢的解决方案。
我同意该解决方案不是优雅的。错误的数据类型包括许多可能的错误,隐含的转换和可扩展性问题,并且有一些方面是多余的,但是OP很快提及,它们仅将其用作FYP的小方面(最后一年项目)主要方面是c#,所以我想尽可能少地更改他们的脚本以将其作为他们自己的工作。
快速修改OP脚本:
UPDATE
Attendance
SET
Hours = 4
WHERE
-- The difference in dates in minutes between...
DATEDIFF(MINUTE,
-- ... DateTime (with a quick data check)...
(IIF(ISDATE([DateTime]) = 1,
CONVERT(DATETIME, [DateTime], 111),
[DateTime]))
-- ... and right now.
, GetDate()
-- Divided by 60 to get
)/60.0 >= 4
-- DateTime has to be equal to today (relying on implicit conversion).
and [DateTime] = convert(Date, GETDATE())