April 2, 2019 at 5:35 am
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
April 2, 2019 at 5:44 am
Did you try using LAG()?
April 2, 2019 at 7:14 am
I tried using LAG but not able to get desired result by portioning 1st set last and second set first value is not working.
April 2, 2019 at 1:33 pm
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.
April 2, 2019 at 2:20 pm
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
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy