Query to get output as I need-CodePudding

I have a table with the below sample output.

ID_Emp| Name    |   Date          
11    |Jonh     |14/05/2014 8:16
11    |Jonh     |14/05/2014 13:35
11    |Jonh     |14/05/2014 17:23
11    |Jonh     |14/05/2014 21:09
12    |Elizabe  |14/05/2014 14:06
12    |Elizabe  |14/05/2014 20:39
12    |Elizabe  |14/05/2014 21:39
12    |Elizabe  |14/05/2014 22:39
13    |Jimmy    |14/05/2014 8:00
13    |Jimmy    |14/05/2014 17:12
13    |Jimmy    |14/05/2014 18:12

I want to build a query to achieve the below results:

ID_Emp|Name  |Date          |TimeIn1 |TimeOut1||TimeIn2|TimeOut2|Hours
11  |Jonh    |14/05/2014    |8:16   |13:35     |17:23  |21:09  |5:19
12  |Elizabe |14/05/2014    |14:06  |20:39     |21:39  |22:39  |8:33
13  |Jimmy   |14/05/2014    |8:00   |17:12     |18:12  |  -    |9:12

Query:

SELECT cio.emp_reader_id, cio.dt AS CheckIn, 
       cio.next_dt as CheckOut
FROM (SELECT cio.*,
             ROW_NUMBER() OVER (PARTITION BY cio.emp_reader_id, CONVERT(date, cio.dt) ORDER BY cio.dt) as seqnum,
             LEAD(cio.dt) OVER (PARTITION BY cio.emp_reader_id, CONVERT(date, cio.dt) ORDER BY cio.dt) as next_dt
      FROM trnevents cio 
     ) cio 
WHERE seqnum % 2 = 1

CodePudding user response：

Multiple CTE is used here for calculation. This query is applicable for each date of every emp_id. Use format function for converting date and hour:minute.

-- SQL Server
WITH cte AS (SELECT ID_Emp
     , Name
     , CAST(tdate AS date) actual_date
     , tdate
     , ROW_NUMBER() OVER (PARTITION BY ID_Emp, CAST(tdate AS date) ORDER BY tdate) row_num
FROM test
), cte2 AS (
    SELECT ID_Emp
         , MAX(Name) Name
         , actual_date
         , MAX(CASE WHEN row_num = 1 THEN tdate END) TimeIn1
         , MAX(CASE WHEN row_num = 2 THEN tdate END) TimeOut1
         , MAX(CASE WHEN row_num = 3 THEN tdate END) TimeIn2
         , MAX(CASE WHEN row_num = 4 THEN tdate END) TimeOut2
    FROM cte
    GROUP BY ID_Emp, actual_date
)
SELECT ID_Emp, Name
     , FORMAT(actual_date, 'dd/MM/yyyy') date
     , FORMAT(TimeIn1, 'HH:mm') TimeIn1
     , FORMAT(TimeOut1, 'HH:mm') TimeOut1
     , FORMAT(TimeIn2, 'HH:mm') TimeIn2
     , FORMAT(TimeOut2, 'HH:mm') TimeOut2
     , CAST((DATEDIFF(second, TimeIn1, TimeOut1)/3600) AS VARCHAR(2))   ':'  
       CAST(((DATEDIFF(second, TimeIn1, TimeOut1)%3600)/60) AS VARCHAR(2)) Hours
FROM cte2
ORDER BY ID_Emp

Using subquery

SELECT t.ID_Emp, t.Name
     , FORMAT(t.actual_date, 'dd/MM/yyyy') date
     , FORMAT(t.TimeIn1, 'HH:mm') TimeIn1
     , FORMAT(t.TimeOut1, 'HH:mm') TimeOut1
     , FORMAT(t.TimeIn2, 'HH:mm') TimeIn2
     , FORMAT(t.TimeOut2, 'HH:mm') TimeOut2
     , CAST((DATEDIFF(second, t.TimeIn1, t.TimeOut1)/3600) AS VARCHAR(2))   ':'  
       CAST(((DATEDIFF(second, t.TimeIn1, t.TimeOut1)%3600)/60) AS VARCHAR(2)) Hours
FROM (SELECT p.ID_Emp
           , MAX(p.Name) Name
           , p.actual_date
           , MAX(CASE WHEN p.row_num = 1 THEN p.tdate END) TimeIn1
           , MAX(CASE WHEN p.row_num = 2 THEN p.tdate END) TimeOut1
           , MAX(CASE WHEN p.row_num = 3 THEN p.tdate END) TimeIn2
           , MAX(CASE WHEN p.row_num = 4 THEN p.tdate END) TimeOut2
      FROM (SELECT ID_Emp
                 , Name
                 , CAST(tdate AS date) actual_date
                 , tdate
                 , ROW_NUMBER() OVER (PARTITION BY ID_Emp, CAST(tdate AS date) ORDER BY tdate) row_num
            FROM test     
           ) p
      GROUP BY ID_Emp, actual_date) t
ORDER BY t.ID_Emp

Please check from this url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=54f542847167481298b168d6b0a8d6b9

CodePudding user response：

enter code here

select Id,Name,Date,TimeIn1,TimeOut1,TimeIn2 from (select Id,Name,Date,time TimeIn1,lead(a.time) over(Partition by a.Id,a.Name,a.Date order by time) as TimeOut1 ,lead(a.time,2) over(Partition by a.Id,a.Name,a.Date order by time) as TimeIn2 ,ROW_NUMBER()Over(Partition by a.Id,a.Name,a.Date order by time) as rn from (select Id,Name,cast(date as date) as date,cast(date as time) as Time from Stack )a)b where rn=1