CODEWITHSUNDEEP
sqldatabaseschema
Wayneio
Wayneio

Reputation: 3566

Constructing an SQL query for schema

I have the following database schema for an attendance system:

database schema

How would I write an SQL query to generate a good report of entries on day X? I need it to generate a report that has

Employee Name | TimeIn | TimeOut
Bob | 10:00 | 11:00
Sam | 10:30 | 18:00
Bob | 11:30 | 15:00

but the row that defines if it was a time in or out is set by entryType (1 being in, 0 being out), so I would aliases TimeIn and TimeOut.

My attempt was

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

but this doesn't handle the fact that some times are entry, some are exit.

Note that there can be multiple sign ins per date.

Update:

Another attempt, but the subquery returns multiple rows

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

Upvotes: 0

Views: 191

Answers (3)

StevieG
StevieG

Reputation: 8709

This works in Oracle (apologies for the non-ANSI style, but you should get the drift)..

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)

Update:

Ah, yes, hadn't considered that. In this case you need an outer join. something like this (untested):

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)

Upvotes: 1

Mariappan Subramanian
Mariappan Subramanian

Reputation: 10063

Simply this will work. Try this

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

Note : I didnt include the entry type because at any time min time will be swipe in and max time will be swipe out

Updated

To show no of sign ins and outs try something like this,

 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

Upvotes: 1

Darshan Mehta
Darshan Mehta

Reputation: 30809

This query will give you the earliest time in and latest time out of an employee.

 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>

Upvotes: 0

Related Questions