A join query

Question

Post reply

A join query

v_krishnan

SSChasing Mays

Points: 616
More actions
November 14, 2005 at 10:17 pm

#166210

I have an application where I have to record the attendance of employees. Therefore, I have a table called Attendance with the following columns -
emp_name varchar, log_time datetime, in_out tinyint.
When an employee enters the office his name, time of entrance is stored in the table, and a value of 1 is stored in 'in_out'. When he leaves the office, the name, time of exit, and a value of 0 is stored in 'in_out'. This way the attendance of every employee is recorded.
Now, I have to prepare a query on this table which returns me the number of hours an employee has reported to office in a given month. I need to generate this per employee.
As you can see, I have the necessary information in the Attendance table. Can someone suggest how to go about this query?
Thanks!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

[KH] SSCommitted Points: 1908 More actions · Answer 1

assumption : time in and time out are of same day

select emp_name, convert(char(7), work_date, 121) as work_month, sum(work_hour) as total_work_hour

from

(

selects.emp_name, convert(datetime, convert(char(8), s.log_time, 112)) as work_date, min(s.log_time) as start_time, max(e.log_time) as end_time,

work_hour = datediff(minute, min(s.log_time), max(e.log_time)) / 60.0

from#log s inner join #log e

ons.emp_name= e.emp_name

andconvert(char(8), s.log_time, 112)= convert(char(8), e.log_time, 112)

wheres.in_out= 1

ande.in_out= 0

group by s.emp_name, convert(datetime, convert(char(8), s.log_time, 112))

) work_daily

group by emp_name, convert(char(7), work_date, 121)

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

________________________________________________________________________________________________________________________

Does NOT require TimeIn/TimeOut be on same day... only requires that for every TimeIn, there is a TimeOut and that there are no "straggler" TimeOut's from the previous month.

SELECT Emp_Name,

CAST(

((SELECT SUM(CAST(Log_Time AS FLOAT)) FROM yourtable WHERE In_Out=0 AND Emp_Name = t.Emp_Name)

-(SELECT SUM(CAST(Log_Time AS FLOAT)) FROM yourtable WHERE In_Out=1 AND Emp_Name = t.Emp_Name))

*24 AS DECIMAL(4,1)) AS TotalHours

FROM yourtable t

GROUP BY Emp_Name

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions