Блог

Главная - Вопросы по программированию - Запрос для получения вывода, как мне нужно

Запрос для получения вывода, как мне нужно

#sql #sql-server #tsql

Вопрос:

У меня есть таблица с приведенным ниже примером вывода.

 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

Я
хочу построить запрос для достижения следующих результатов:

 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

Запрос:

 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

Комментарии:

1. Можете ли вы добавить операторы DDL и insert для таблицы, чтобы попробовать, пожалуйста

2. Будете ли вы рассчитывать 4 данные в день.

3. Для Элизабет часы были бы 6:33, а не 8:33. Часы рассчитываются между тайм — аутом1- тайм-ау1

Ответ №1:

Для расчета здесь используется несколько CTE. Этот запрос применим для каждой даты каждого emp_id. Используйте функцию форматирования для преобразования даты и часа:минута.

 -- 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)600)/60) AS VARCHAR(2)) Hours
FROM cte2
ORDER BY ID_Emp

Использование подзапроса

 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)600)/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

Пожалуйста, проверьте по этому адресу https://dbfiddle.uk/?rdbms=sqlserver_2017amp;fiddle=54f542847167481298b168d6b0a8d6b9

Ответ №2:

 enter code here

выберите идентификатор,имя,дата,TimeIn1,TimeOut1,TimeIn2
из (выберите идентификатор,имя,дата,время TimeIn1,свинец (.время) над(раздел по.Удостоверение личности.Имя.Дата заказа по времени), как TimeOut1
,свинец (.время,2) над(раздел по.Удостоверение личности.Имя.Дата заказа по времени), как TimeIn2
,Функции row_number()над(раздел по.Удостоверение личности.Имя.Дата заказа по времени), а рН
из (выберите ID,имя,литой(дата, день) как дата,литой(дата, время) как раз
из стека )а)б где RN=1

Метки: ,