Построение SQL-запроса для схемы

У меня есть следующая схема базы данных для системы посещаемости:

схема базы данных

Как мне написать SQL-запрос, чтобы создать хороший отчет о записях в день X? Мне нужно, чтобы создать отчет, который

Имя сотрудника | время в | TimeOut
Боб | 10:00 | 11:00
Сэм | 10:30 | 18:00
Боб | 11:30 | 15:00

но строка, которая определяет, было ли это время входа или выхода, устанавливается с помощью entryType (1 — вход, 0 — выход), поэтому я бы использовал псевдонимы TimeIn и TimeOut.

Моя попытка была

`SELECT firstName, time from log INNER JOIN users on log.employeeID = users.employeeID WHERE date = GETDATE()`

но это не учитывает тот факт, что иногда вход, а иногда выход.

Обратите внимание, что за одну дату может быть несколько входов в систему.

Обновлять:

Еще одна попытка, но подзапрос возвращает несколько строк

select firstName, (select time as timeIn from log where entryType = 1), (select time as timeOut from log where entryType = 0) inner join users on log.uID = users.uID from log group by uID

sql database schema
person Wayneio    schedule 05.07.2013    source источник
comment
Традиционно попробовать самому, а не просить кого-то сделать все за вас. Если у вас есть конкретные вопросы/проблемы, возвращайтесь с более конкретными вопросами.   -  person RiggsFolly    schedule 05.07.2013
comment
как определяются TimeIn и TimeOut на основе атрибутов вашей таблицы?   -  person Brian    schedule 05.07.2013
comment
@RiggsFolly моя конкретная проблема связана с отображением столбца времени прихода и времени ожидания в отчете. Строка, которая определяет, была ли запись входом или выходом, имеет тип entryType, который равен 1 для входа и 0 для выхода.   -  person Wayneio    schedule 05.07.2013

Ответы (3)


arrow_upward
1
arrow_downward

Это работает в Oracle (извините за стиль, отличный от ANSI, но вы должны понять дрейф).

SELECT FORENAME,SURNAME,L1.TIME IN_TIME,L2.TIME OUT_TIME
FROM EMPLOYEES EMP, LOG L1, LOG L2
WHERE EMP.EMPLOYEE_ID = L1.EMPLOYEE_ID
AND EMP.EMPLOYEE_ID = L2.EMPLOYEE_ID
AND L1.ENTRYTYPE = 1
AND L2.ENTRYTYPE = 0
AND L2.TIME = (SELECT MIN(TIME) FROM LOG WHERE EMPLOYEE_ID = L2.EMPLOYEE_ID AND L2.ENTRYTYPE = 0 AND TIME > L1.TIME)

Обновлять:

А, да, не подумал об этом. В этом случае вам нужно внешнее соединение. что-то вроде этого (не проверено):

SELECT FORENAME,SURNAME,L1.TIME IN_TIME,L2.TIME OUT_TIME
FROM EMPLOYEES EMP
INNER JOIN LOG L1 ON EMP.EMPLOYEE_ID = L1.EMPLOYEE_ID AND L1.ENTRYTYPE = 1
LEFT OUTER JOIN LOG L2 ON EMP.EMPLOYEE_ID = L2.EMPLOYEE_ID AND L2.ENTRYTYPE = 0
                       AND L2.TIME = (SELECT MIN(TIME) FROM LOG WHERE EMPLOYEE_ID = L2.EMPLOYEE_ID AND L2.ENTRYTYPE = 0 AND TIME > L1.TIME)
person StevieG    schedule 05.07.2013
comment
Это работало отлично, пока я не заметил, что если пользователь вошел в систему, но еще не вышел из нее, он не будет отображать строку! - person Wayneio; 05.07.2013

arrow_upward
1
arrow_downward

Просто это будет работать. Попробуй это

SELECT FORENAME,SURNAME,LG.IN_TIME,LG.OUT_TIME FROM EMPLOYEES EMP INNER JOIN 
(SELECT MIN(TIME) IN_TIME,MAX(TIME) OUT_TIME,EMPLOYEE_ID FROM LOG
GROUP BY EMPLOYEE_ID) LG ON EMP.EMPLOYEE_ID=LG.EMPLOYEE_ID

Примечание. Я не указал тип записи, потому что в любой момент минимальное время будет пролистываться, а максимальное время будет пролистываться.

Обновлено

Чтобы не показывать входы и выходы, попробуйте что-то вроде этого:

 SELECT FORENAME,SURNAME,LG.IN_TIME,LG.OUT_TIME,LG.no_of_ins,
LG.no_of_outs FROM EMPLOYEES EMP INNER JOIN 
(SELECT MIN(TIME) IN_TIME,MAX(TIME) OUT_TIME,EMPLOYEE_ID,
COUNT( CASE WHEN ENTRY_TYPE='I' THEN 1 ELSE O END noi) no_of_ins,
COUNT( CASE WHEN ENTRY_TYPE='O' THEN 1 ELSE O END nou) no_of_outs,
GROUP BY EMPLOYEE_ID) LG ON EMP.EMPLOYEE_ID=LG.EMPLOYEE_ID
person Mari    schedule 05.07.2013
comment
Спасибо, это близко, но проблема в том, что если есть несколько входов в один день, он покажет первый вход (в столбце time_in) и последний выход (в столбце time_out), но не несколько войти в систему. - person Wayneio; 05.07.2013
comment
Это хорошо, но я надеюсь получить строки для нескольких входов, как показано пользователем bob в моем обновленном вопросе. Я думаю, что это может быть невозможно сейчас. - person Wayneio; 05.07.2013

arrow_upward
0
arrow_downward

Этот запрос даст вам самое раннее время прихода и самое позднее время ухода сотрудника.

 SELECT E.FORENAME, 
(SELECT MIN(TIME) FROM LOG WHERE EMPLOYEEID = E.EMPLOYEEID AND ENTRYTYPE = 1 AND DATE = <YOUR DAYE>) AS "TIME_IN", 
(SELECT MAX(TIME) FROM LOG WHERE EMPLOYEEID = E.EMPLOYEEID AND ENTRYTYPE = 0 AND DATE = <YOUR DAYE>) AS "TIME_OUT" 
FROM EMPLOYEE E WHERE E.EMPLOYEEID = <EMPLOYEE ID>
person Darshan Mehta    schedule 05.07.2013