Конструиране на SQL заявка за схема

Имам следната схема на база данни за система за присъствие:

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

Как да напиша SQL заявка, за да генерирам добър отчет за записи в ден X? Трябва ми, за да генерирам отчет, който има

Име на служител | TimeIn | Време за изчакване
Боб | 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