Need Help to calculate time difference in same column in sql

Question

Need Help to calculate time difference in same column in sql

venkatesh9490

SSC Enthusiast

Points: 172
More actions
June 17, 2020 at 12:10 pm

#3763838

I have employee login date and time from the login date and time i need calculate log out date and time.
In a day employee login first time as some activity and next time he's login in different activity so that i need to calculate middle time so that will be logout time for that activity
data looks like

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

The short answer to your question is to use the LAG function to get the value from the previous record. Something like this...

LAG([LoginTime],1) OVER (PARTITION BY EmpID ORDER BY [LoginTime])

Oh right.. where are my manners? Welcome to SSC! Not bad for a first post! One post most of us consider required reading is Jeff Moden's article on how to ask a question so that it will get answered, and that's this one: Forum Etiquette: How to post data/code on a forum to get the best help

(I changed the name of your column in my answer because TIME is a reserved word in T-SQL.)

Since you're new, here's one on the house...

--  SETUP: Create and populate table(s).
use tempdb;
go

CREATE TABLE EmpLogins (
LogNo INT IDENTITY,
LoginOut DATETIME NOT NULL,
EmpID INT NOT NULL,
Activity CHAR(3) NOT NULL
);
GO

INSERT INTO EmpLogins (LoginOut, EmpID, Activity)
VALUES ('6/16/2020 14:09:17', 245, 'APS'),
('6/16/2020 14:25:17', 245, 'KS'),
('6/16/2020 15:09:17', 245, 'APS'),
('6/16/2020 15:09:17', 245, 'TG'),
('6/14/2020 14:11:17', 999, 'APS');

Solution:

SELECT LogNo
, EmpID
, Activity
, LogInOut
, PrevLogInOut = LAG(LogInOut,1) OVER (PARTITION BY EmpID ORDER BY LogInOut)
, TimeSpent = DATEDIFF(second,LAG(LogInOut,1) OVER (PARTITION BY EmpID ORDER BY LogInOut) ,LoginOut)
FROM EmpLogins;

The reason you should include the setup (create table(s) and Insert scripts) is that it helps everybody here to help you, because they can just run the script you provided and have (at least) a representation of your data. Then you get tested answers.

Okay, here's a tested answer <g>:

SELECT LogNo
, LogInOut
, EmpID
, Activity
, LAG(LogInOut,1) OVER (PARTITION BY EmpID ORDER BY LogInOut) AS PrevLogin
, DATEDIFF(second,LAG(LogInOut,1) OVER (PARTITION BY EmpID ORDER BY LogInOut),LogInOut)
FROM EmpLogins;

Welcome and happy learning!

Pieter

This reply was modified 4 years, 5 months ago by pietlinden.
This reply was modified 4 years, 5 months ago by pietlinden.

mitm786 SSC Enthusiast Points: 174 More actions · Answer 2

In this paragraph, we have to give an example of the time difference with SQL you can see the below

Example :- TIMEDIFF(time_first, time_Secount)

This is the main syntax of time diff if you want more relative example go to following this SQL Insert Multiple Rows