How to calculate time difference between two dates portion by attendance

Question

Post reply

How to calculate time difference between two dates portion by attendance

Suman152

SSC Enthusiast

Points: 113

April 2, 2019 at 5:35 am

#2458312

Dear Experts,

i need help in calculating time difference between two dates portion by Date, the difference will be calculated with the first day last Attendance = 1 with very next Attendance =1 of next day. and same will repeat second day last Attendance = 1 with very next Attendance =1 of next day.

Sample data SQL:

Drop Table #tmp_employees
CREATE TABLE #tmp_employees
(
Name VARCHAR(30) NULL,
EmployeNo VARCHAR(30) NULL,
Date DATETIME NULL,
Value INT NULL,
Attendence INT NULL
--,[Calculate Hr's difference] INT NULL
)
GO
insert into #tmp_employees
Values
('John','123','3/29/19 9:00 AM','10','0'),
('Raju','234','3/29/19 10:00 AM','20','0'),
('Chris','345','3/29/19 10:00 AM','50','1'),
('Ram','654','3/29/19 9:00 AM','10','1'),
('Robart','345','3/29/19 12:00 PM','10','0'),
('John','123','3/30/19 9:00 AM','10','0'),
('Raju','234','3/30/19 9:10 AM','20','1'),
('Chris','345','3/30/19 9:00 AM','50','1'),
('Ram','654','3/30/19 9:00 AM','10','1'),
('Robart','345','3/30/19 9:00 AM','10','0'),
('John','123','3/31/19 9:00 AM','10','0'),
('Raju','234','3/31/19 9:20 AM','20','1'),
('Chris','345','3/31/19 9:00 AM','50','0'),
('Ram','654','3/31/19 9:00 AM','10','0')

Expected output

Please help me in achieving above scenario.

Thanks in Advance

Viewing 5 posts - 1 through 5 (of 5 total)

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

pietlinden SSC Guru Points: 63597 More actions · Answer 1

pietlinden

SSC Guru

Points: 63597

April 2, 2019 at 5:44 am

#2459745

Did you try using LAG()?

Suman152 SSC Enthusiast Points: 113 More actions · Answer 2

I tried using LAG but not able to get desired result by portioning 1st set last and second set first value is not working.

David Burrows SSC Guru Points: 65144 More actions · Answer 3

SELECT[Name],EmployeNo,[Date],[Value],Attendence,
CASE WHEN PreviousAttendence = 1
AND Attendence = 1
AND DATEDIFF(day,PreviousDate,[Date]) = 1
THEN CAST(PreviousDate as varchar(20)) + ' - ' + CAST([Date] as varchar(20))
END AS [Calculate Hr's difference]
FROM(
SELECTe.[Name],e.EmployeNo,e.[Date],e.[Value],e.Attendence,
LAG(Attendence,1,0) OVER (PARTITION BY e.EmployeNo ORDER BY [Date]),
LAG([Date],1,0) OVER (PARTITION BY e.EmployeNo ORDER BY [Date])
FROM#tmp_employees e
) x ([Name],EmployeNo,[Date],[Value],Attendence,PreviousAttendence,PreviousDate)
ORDER BY EmployeNo,[Date];

Far away is close at hand in the images of elsewhere.
Anon.

drew.allen SSC Guru Points: 77014 More actions · Answer 4

Something like the following:

SELECT *, DATEDIFF(HOUR, LAG(attendance_date) OVER(PARTITION BY EmployeNo ORDER BY a.attendance_date), attendance_date)
FROM #tmp_employees
CROSS APPLY ( VALUES( CASE WHEN Attendence = 1 THEN [Date] END ) ) a(attendance_date)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA