SQL View Query需要在2个日期之间计算时间间隔

I have below View Query. While calculating time interval it only display time interval if check Out is for same date. For example if i check in on 11/12/2017 then it i have to check out with same date 11/12/201 n order to calculate time Interval.

我在View Query下面。在计算时间间隔时,如果检出的是同一日期,则仅显示时间间隔。例如,如果我在2017年12月12日办理登机手续,那么我必须在同一日期11/12/201结账,以便计算时间间隔。

But i want to modify it like if i checkout on next day (after 12:00 AM midnight) it should also calculate the time interval.can some help me to modify query to get the desired results?

但我想修改它,就像我在第二天结账(午夜12:00之后),它还应该计算时间间隔。有些人帮我修改查询以获得所需的结果?

Query:

ALTER VIEW [dbo].[TimeAttendanceQuery]
AS
SELECT TOP (100)    PERCENT 
    dbo.AxPerson.Name,
    dbo.AxPerson.IdNumber  AS EmployeeID,
    dbo.TimeAttendance.Badge, 
    dbo.AxPerson.Id, 
    MIN(dbo.TimeAttendance.EventTime) AS EntryTime, 

    MAX(dbo.TimeAttendance.EventTime) AS ExitTime,
    CAST(DATEDIFF(second, MIN(dbo.TimeAttendance.EventTime), MAX(dbo.TimeAttendance.EventTime)) / 60 / 60 / 24 AS NVARCHAR(50)) +
    ':' + CAST(DATEDIFF(second, MIN(dbo.TimeAttendance.EventTime), MAX(dbo.TimeAttendance.EventTime)) / 60 / 60 % 24 AS NVARCHAR(50)) + ':' + CAST(DATEDIFF(second, MIN(dbo.TimeAttendance.EventTime),  MAX(dbo.TimeAttendance.EventTime)) / 60 % 60 AS NVARCHAR(50))
    AS TimeInterval, 
    dbo.TimeAttendance.Event, 
    dbo.AxPerson.Type AS ShitType, 
    dbo.AxPerson.ShiftDesc, 
    CONVERT(Varchar,dbo.TimeAttendance.EventTime, 101) AS EventTIME

FROM
    dbo.AxPerson 
INNER JOIN dbo.TimeAttendance ON dbo.AxPerson.Name = dbo.TimeAttendance.Name
GROUP BY  dbo.AxPerson.Name, dbo.AxPerson.IdNumber, dbo.TimeAttendance.Badge, CONVERT(Varchar, dbo.TimeAttendance.EventTime, 101), dbo.         AxPerson.ShiftDesc, dbo.AxPerson.Id, dbo.TimeAttendance.Event,dbo.AxPerson.Type
ORDER BY dbo.AxPerson.Name, EventTime DESC

GO

1 个解决方案

#1

I'm not sure about the algorithm you posted, but if you want to get the time difference from two datetimes you can cast the subtraction as time. This works for less than 24 hours. If you also want the number of days (I think this is only good for less than a year), then you can do the datepart-day of the difference.

我不确定你发布的算法,但如果你想从两个日期时间得到时间差,你可以把减法作为时间。这工作不到24小时。如果你也想要天数(我认为这只有不到一年的时间),那么你可以做差异的日期部分。

For example:

DECLARE @starttime datetime = '2017-11-12 010:20:00'
DECLARE @endtime   datetime = '2017-11-13 08:00:00'

SELECT DATEPART(DAY, @endtime - @starttime) - 1 [Days Passed]
      ,CAST(@endtime - @starttime as time(0)) [Time Passed]
       --WHERE the (0) in time(0) is for the milliseconds to return.

Gives output:

Days Passed Time Passed
0           21:40:00

If you don't care about the days, then your code could be modified like this:

如果您不关心这些日子,那么您的代码可以像这样修改:

ALTER VIEW [dbo].[TimeAttendanceQuery]
AS
SELECT Name, EmployeeID, Badge, Id, EntryTime,
       CAST(ExitTime - EntryTime as time(0)) [TimeInterval],
       Event, ShiftType, ShiftDesc
      ,CONVERT(Varchar, EventTime, 101) AS EventTIME
  FROM (
         SELECT
           dbo.AxPerson.Name,
           dbo.AxPerson.IdNumber  AS EmployeeID,
           dbo.TimeAttendance.Badge, 
           dbo.AxPerson.Id, 
           MIN(dbo.TimeAttendance.EventTime) AS EntryTime,  
           MAX(dbo.TimeAttendance.EventTime) AS ExitTime,             
           dbo.TimeAttendance.Event, 
           dbo.AxPerson.Type AS ShiftType, 
           dbo.AxPerson.ShiftDesc, 
           dbo.TimeAttendance.EventTime

           FROM dbo.AxPerson INNER JOIN dbo.TimeAttendance 
                                     ON dbo.AxPerson.Name = dbo.TimeAttendance.Name

           GROUP BY  
           dbo.AxPerson.Name, 
           dbo.AxPerson.IdNumber, 
           dbo.TimeAttendance.Badge, 
           dbo.AxPerson.Id, 
           dbo.TimeAttendance.Event,
           dbo.AxPerson.Type
           dbo.AxPerson.ShiftDesc,
           dbo.TimeAttendance.EventTime,
       ) AS dT

#1