How to calculate time difference between two dates portion by attendance

  • 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

  • Did you try using LAG()?

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

  • 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.

  • 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