Как вывести первое и последнее значение за день в горизонтальный отчёт?

Question

[alexandrsemen4ukk]

База данных на проходной. Каждый день туда пишутся события. Как сделать отчёт в Microsoft Report Builder, в который выводить ФИО сотрудника, первый вход и последний выход за день на протяжении месяца.

например

Как сформировать такой запрос?

Comments

[Константин Цветков]

Никак. В стандарте SQL нет горизонтальных таблиц.
Приведите вашу СУБД и будет ответ как сделать на ней PIVOT.
Microsoft Report Builder — это MS SQL, для Microsoft SQL Server Reporting Services.
В q628559 есть пример календарного отчёта.

[alexandrsemen4ukk]

Константин Цветков, Да, соответственно я использую MS SQL

Answer 1

[Константин Цветков]

SELECT [ФИО], 1 AS Line,
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], 
       [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
  FROM 
    ( SELECT [ФИО], 
             DAY([Событие]) AS [День],
             FORMAT([Событие], 'HH:mm') AS [Время]
        FROM [Проходная]
        WHERE FORMAT([Событие], 'yyyy-MM') = @P) AS enDay
    PIVOT ( MIN([Время])
      FOR [День] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
                      [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], 
                      [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
        ) AS enPivot
UNION
SELECT [ФИО], 2,
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], 
       [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
  FROM 
    ( SELECT [ФИО], 
             DAY([Событие]) AS [День],
             FORMAT([Событие], 'HH:mm') AS [Время]
        FROM [Проходная]
        WHERE FORMAT([Событие], 'yyyy-MM') = @P) AS enDay
    PIVOT ( MAX([Время])
      FOR [День] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
                      [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], 
                      [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
          ) AS enPivot
UNION
SELECT [ФИО], 3,
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], 
       [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
  FROM 
    ( SELECT [ФИО],
             DAY([Событие]) AS [День],
             FORMAT(MAX([Событие]) - MIN([Событие]), 'HH:mm') AS [Время]
        FROM [Проходная]
        WHERE FORMAT([Событие], 'yyyy-MM') = @P
        GROUP BY [ФИО], DAY([Событие])) AS enDay
    PIVOT ( MAX([Время])
      FOR [День] IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
                      [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], 
                      [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
           ) AS enPivot
  ORDER BY 1, 2

Список для параметра:

SELECT DISTINCT FORMAT([Событие], 'yyyy-MM')
  FROM [Проходная]
  ORDER BY 1 DESC

Значение по-умолчанию для параметра:

SELECT FORMAT(MAX([Событие]), 'yyyy-MM')
  FROM [Проходная]

Comments

[alexandrsemen4ukk]

Спасибо за ваше решение, оно кажется верным, однако я сталкиваюсь с тем, что не могу понять как подставить select из воженного запроса. И я так понимаю, что это работает если каждого сотрудника вручную указываем, автоматически по всем сотрудникам это не подтянется?

[Константин Цветков]

alexandrsemen4ukk, enDay.FullName

[Константин Цветков]

alexandrsemen4ukk, Да, и DECLARE @P VARCHAR(7) = '2022-09' только для теста. Для построителя отчётов параметр не нужно декларировать. Он будет настраиваться и использоваться в самом отчёте.

[Константин Цветков]

alexandrsemen4ukk, И ещё: в SSMS параметр запрашивается при выполнении запроса. То есть "Создать запрос", помещаете туда основной запрос (и его же в построитель отчётов) и "Выполнить".

[alexandrsemen4ukk]

Константин Цветков, Спасибо Вам! Это сработало, осталось разобраться как это работает :)

[Модератор]

alexandrsemen4ukk, В Windows есть неочевидная фича: Ctrl+C копирует с активного диалога весь текст с него в буфер обмена.

[alexandrsemen4ukk]

Модератор, спасибо за подсказку, но для меня более неочевидно к чему Вы это написали?)

[Модератор]

alexandrsemen4ukk, И вижу удалённое :-)

[alexandrsemen4ukk]

Модератор, и всё равно продолжаю не понимать зачем Вы это пишите)

[Модератор]

alexandrsemen4ukk, Потому что у вас картина вместо текста.

[alexandrsemen4ukk]

Модератор, мне кажется очевидным, что это сделано с целью показать места возникновения ошибки и её содержимого. Чтобы отвечающий на вопрос пользователь наглядно видел где и какая проблема, и мне не пришлось бы печатать 5000 символов для подробного описания

[alexandrsemen4ukk]

Константин Цветков, Ещё вопрос, возможно вы знаете, как в microsoft report builder я могу вывеси отработанное время за день. Нагуглить ничего не смог, попытался исправить запрос, который вы прислали, но у меня ничего не получилось. Вот я и думаю, как вывести отработанное время
Ещё раз спасибо!

[Константин Цветков]

alexandrsemen4ukk,

отработанное время

Добавил, строка номер 3.

[alexandrsemen4ukk]

Константин Цветков, Именно так я переписывал самостоятельно, но всё равно получал ошибку

[alexandrsemen4ukk]

Решил проблему заменив

FORMAT(MAX([Событие]) - MIN([Событие]), 'HH:mm') AS [Время]

на

CAST(MAX(Log.DateTime) as datetime) - CAST(MIN(Log.DateTime) as datetime) AS Time

. Теперь всё работает как надо! Ещё раз спасибо Константин Цветков за решение моей головной боли!

[Константин Цветков]

alexandrsemen4ukk, Да, у вас "неправильный" :-) datetime2 тип данных у поля Log.DateTime.

[alexandrsemen4ukk]

Константин Цветков, тут возник ещё один вопрос,

DECLARE @P VARCHAR(7) = FORMAT(@StartDate, 'yyyy-MM')

SELECT enPivot.FullName, enPivot.Name, 'Пришел' AS Line,
     [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], 
     [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM 
  ( SELECT Employee.FullName, 
     IncidentOrganizationDictionary.Name,
           DAY(Log.DateTime) AS Day,
           FORMAT(Log.DateTime, 'HH:mm') AS Time

      FROM Log
  INNER JOIN Employee ON Employee._id = Log.EmployeeID
  INNER JOIN Organization2EmployeeAssignment ON Organization2EmployeeAssignment.EmployeeID = Log.EmployeeID
  INNER JOIN IncidentOrganizationDictionary ON IncidentOrganizationDictionary._id = Organization2EmployeeAssignment.OrganizationID
      WHERE FORMAT(Log.DateTime, 'yyyy-MM') = @P AND IncidentOrganizationDictionary.Name IS NULL) AS enDay
  PIVOT ( MIN(Time)
    FOR Day IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
                    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], 
                    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
      ) AS enPivot
UNION
SELECT enPivot.FullName, enPivot.Name, 'Ушел',
     [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], 
     [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM 
( SELECT Employee.FullName, 
    IncidentOrganizationDictionary.Name,
           DAY(Log.DateTime) AS Day,
           FORMAT(Log.DateTime, 'HH:mm') AS Time
      FROM Log
  INNER JOIN Employee ON Employee._id = Log.EmployeeID
  INNER JOIN Organization2EmployeeAssignment ON Organization2EmployeeAssignment.EmployeeID = Log.EmployeeID
  INNER JOIN IncidentOrganizationDictionary ON IncidentOrganizationDictionary._id = Organization2EmployeeAssignment.OrganizationID
      WHERE FORMAT(Log.DateTime, 'yyyy-MM') = @P AND IncidentOrganizationDictionary.Name IS NULL) AS enDay
  PIVOT ( MAX(Time)
    FOR Day IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
                    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], 
                    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
        ) AS enPivot
UNION
SELECT enPivot.FullName, enPivot.Name, 'Отработал',
     [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], 
     [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]
FROM 
  ( SELECT Employee.FullName,
    IncidentOrganizationDictionary.Name,
           DAY(Log.DateTime) AS Day,
           CAST(MAX(Log.DateTime) as datetime) - CAST(MIN(Log.DateTime) as datetime) AS Time
      FROM Log
  INNER JOIN Employee ON Employee._id = Log.EmployeeID
  INNER JOIN Organization2EmployeeAssignment ON Organization2EmployeeAssignment.EmployeeID = Log.EmployeeID
  INNER JOIN IncidentOrganizationDictionary ON IncidentOrganizationDictionary._id = Organization2EmployeeAssignment.OrganizationID
      WHERE FORMAT(Log.DateTime, 'yyyy-MM') = @P AND IncidentOrganizationDictionary.Name IS NULL
      GROUP BY Employee.FullName, IncidentOrganizationDictionary.Name, DAY(Log.DateTime)) AS enDay
  PIVOT ( MAX(Time)
    FOR Day IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], 
                    [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], 
                    [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
         ) AS enPivot

ORDER BY Line DESC

Захотел я всё тоже самое сделать для тех, у кого организация = NULL. Добавил в WHERE проверку

WHERE FORMAT(Log.DateTime, 'yyyy-MM') = @P AND IncidentOrganizationDictionary.Name IS NULL

, а список не возвращается. Если же пишу IS NOT NULL - всё ок, всех у кого организации есть выводит. Такое ощущение как будто возвращается список только тех, у кого организация указана. Но если я сделаю обычный запрос всех, кто без организации, то выводит

[Константин Цветков]

alexandrsemen4ukk, Непонятно: INNER JOIN не может быть IS NULL. Что не так? Может нужно в основном запросе использовать LEFT JOIN?

[alexandrsemen4ukk]

Константин Цветков, И главное в тестовом запросе написал через LEFT, а в основном коде не поменял *тупица*
Спасибо, в очередной раз выручили!

Answer 2

[Сергей Томулевич]

SELECT
    fio,
    date,
    MIN(reg_time) AS come_time,
    MAX(reg_time) AS out_time
FROM logtable
GROUP BY fio, date;