CODEWITHSUNDEEP
sql-server
Mike
Mike

Reputation: 139

Calculate the datediff() between the date 1 and 2

I need to calculate the datediff from one column where the in time marked with a 1 and the out time marked with a 2. if an employee swiped in and there is no out or out but there was no in i would like it to show as null. I am not sure how I go about doing this.

SELECT 
    u.userid 
   ,et.name
   ,CASE 
    WHEN scs.FullName is NULL THEN u.name
    WHEN scs.FullName is NOT NULL THEN scs.FullName
    END AS name
    ,e.LoggedTime AS SwipeTime
    ,CASE 
        WHEN et.name = 'Output On By Door' OR et.name = 'User Granted 
Entry To Door Using Reading Device' THEN 1
        ELSE 2
    END AS SwipeTimeDiff
    ,d.name AS Door
FROM [Users] AS u
LEFT JOIN [Events] AS e ON e.RecordIndex1=u.UserID
LEFT JOIN [EventTypes] AS et on e.EventTypeID = et.EventTypeID
join .[Doors] AS d ON e.RecordIndex2 = d.DoorID
LEFT join SecurityContractorSignIn as scs on scs.Badge = u.lastname
WHERE LoggedTime > CONVERT(DATE, GETDATE()) and d.doorid in (32, 50, 42, 51, 33) 
ORDER BY u.name,e.LoggedTime DESC

I would like to have a computed column with the time difference in days, hours and minutes or null if if there is a missing in(1) or out(2) time.

Upvotes: 0

Views: 235

Answers (1)

cdrrr
cdrrr

Reputation: 1114

Well, the DATEDIFF() function is fully explained here and the difference for the specific datepart you want to extract is returned as an integer.

According to your need you may do something like but you will have the information in three (or more - if you want to extend) different columns:

-- Calculate the difference of how many days have passed 
SELECT DATEDIFF(DAY, LoginTime, LogoutTime) AS DaysPassed

-- Calculate the difference of how many hours have passed 
SELECT DATEDIFF(HOUR, LoginTime, LogoutTime) AS HoursPassed

-- Calculate the difference of how minutes have passed 
SELECT DATEDIFF(MINUTE, LoginTime, LogoutTime) AS MinutesPassed

If you want to return a string whether the employee logged out or not you may use something like:

SELECT ISNULL(CONVERT(nvarchar(50), DATEDIFF(MONTH, '2019-01-04', NULL)), 'No logout')

Upvotes: 1

Related Questions