Search code examples
sql-servertelerik

FirstIn and LastOut in sqlserver

I have a table name [EmployeeAttendance] and I have records in it in respective order.

SELECT EI.[FirstName]+' '+EI.[LastName] [EmployeeName], [Dpt].[FullName] [Department], [Desig].[FullName] [Designation], FirstIN   = CAST(MIN([AttendanceTimeIn]) AS TIME), LastOUT   = CAST(MAX([AttendanceTimeOut]) AS TIME), HoursSpent = DATEDIFF(HOUR, CAST(MIN(AttendanceTimeIn) AS TIME), CAST(MAX(AttendanceTimeOut) AS TIME))  
FROM [HRM].[tbl_EmployeeInfo] [EI],  [HRM].[tbl_Designation] [Desig], [HRM].[tbl_Department] [Dpt], [HRM].[tbl_EmployeeAttendance] [Attendance]
WHERE [Dpt].[ID] = [EI].[DeptCode] AND [Desig].[ID] = [EI].[DesignationCode] AND [Attendance].[EmpCode] = [EI].[ID] AND [EI].[RecordStatusCode] != '13'
AND CAST([AttendanceTimeIn] as date) = CAST(GetDate()-1 as Date)
GROUP BY
EI.[FirstName]+' '+EI.[LastName], [Dpt].[FullName], [Desig].[FullName], CAST([Attendance].[AttendanceTimeIn] AS DATE)

This is what I am getting as output.

Ajmal John      Projects            Project Associate   10:16:38.0000000    NULL    NULL
Asif Asif       Office Staff        Office Boy               09:28:36.0000000   NULL    NULL
Muhammad Asim   Support             Database Support Engineer 10:47:28.0000000  19:16:17.0000000    9
Sajjad Ahmed    Projects            Project Manager          09:41:34.0000000   NULL    NULL
Sidra Khizar    Quality Assurance   SQA Engineer        10:18:48.0000000    NULL    NULL

Because I have placed TimeIn and TimeOut in same row it is giving me TimeOut but as for other fields I have Time Out in 2nd row so it is giving Null. Not sure why

Solution

  • If TimeIn and TimeOut are of TIME datatype then this works

    DECLARE @EmployeeAttendance TABLE (EmpID INT, TimeIn TIME, [TimeOut] TIME);

INSERT INTO @EmployeeAttendance
(EmpID, TimeIn, TimeOut)
SELECT 1, '9:00', NULL UNION ALL
SELECT 1, NULL, '11:00' UNION ALL
SELECT 1, '11:30', NULL  UNION ALL
SELECT 1, NULL,'13:00' UNION ALL
SELECT 1,'13:30', NULL  UNION ALL
SELECT 1,NULL,'18:00';

SELECT
    EmpID
    , FirstIN   = MIN([TimeIn])
    , LastOUT   = MAX([TimeOut])
    , HoursSpent = DATEDIFF(HOUR, MIN(TimeIn), MAX(TimeOut))
    --, MAX([TimeOut])-MIN([TimeIn]) AS HoursSpent
FROM
    @EmployeeAttendance
GROUP BY
    EmpID;

    Output

    enter image description here

    If the columns are DATETIME then try this

    DECLARE @EmployeeAttendance TABLE (EmpID INT, TimeIn DATETIME, [TimeOut] DATETIME);

INSERT INTO @EmployeeAttendance
(EmpID, TimeIn, TimeOut)
SELECT 1, '9:00', NULL UNION ALL
SELECT 1, NULL, '11:00' UNION ALL
SELECT 1, '11:30', NULL  UNION ALL
SELECT 1, NULL,'13:00' UNION ALL
SELECT 1,'13:30', NULL  UNION ALL
SELECT 1,NULL,'18:00';

SELECT
    EmpID
    , FirstIN   = CAST(MIN([TimeIn]) AS TIME)
    , LastOUT   = CAST(MAX([TimeOut]) AS TIME)
    , HoursSpent = DATEDIFF(HOUR, CAST(MIN(TimeIn) AS TIME), CAST(MAX(TimeOut) AS TIME))
    --, MAX([TimeOut])-MIN([TimeIn]) AS HoursSpent
FROM
    @EmployeeAttendance
GROUP BY
    EmpID;