display employee in or out - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

display employee in or out

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-16 : 18:30:15

I need to create a view to display employee id and time in/out status. My view should look like this since i see that employee has not time out (refer to second row). I wrote sql but it's not giving me this result.

emp id clocking Status
12 in

Data
id c_date time_in time_out
12 2009-01-07 2009-01-07 06:21:42.000 2009-01-07 12:04:22.000
12 2009-01-07 2009-01-07 12:51:57.000 NULL

= = = broken sql = = =

SELECT TOP (100) PERCENT dbo.CLOCKING_DETAILS.emp_id, MAX(dbo.CLOCKING_DETAILS.clocking_date) AS Clocking_Dt,
MAX(DISTINCT dbo.CLOCKING_DETAILS.time_in) AS [in], MIN(DISTINCT dbo.CLOCKING_DETAILS.time_out) AS out
FROM dbo.CLOCKING_DETAILS INNER JOIN
dbo.EMPLOYEES ON dbo.CLOCKING_DETAILS.emp_id = dbo.EMPLOYEES.emp_id
WHERE (dbo.EMPLOYEES.active = 1)
GROUP BY dbo.CLOCKING_DETAILS.emp_id
HAVING (dbo.CLOCKING_DETAILS.emp_id = 12)
ORDER BY dbo.CLOCKING_DETAILS.emp_id

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-17 : 02:14:42

This seems to be doing but need more testing.

SELECT cd.emp_id, MIN(CASE WHEN (cd.time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus
FROM dbo.CLOCKING_DETAILS AS cd INNER JOIN
dbo.EMPLOYEES AS e ON cd.emp_id = e.emp_id
WHERE (e.active = 1)
GROUP BY cd.emp_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 03:04:01

you didnt give any info about what all fields you've in your table. IF you can give the details with some sample data, we will be able to help you on this.

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-17 : 13:42:11

this is from clocking_details
Data
id c_date time_in time_out
12 2009-01-07 2009-01-07 06:21:42.000 2009-01-07 12:04:22.000
12 2009-01-07 2009-01-07 12:51:57.000 NULL

emp date
emp_id
1
2
3
4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 13:45:38

and how are they linked? does ID in data represent empid values?

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-17 : 14:21:38

does this help. also, you can take out the employee table. I can join that later. I just need the names from employee table, other than that employee table is not playing any vital role for this sql.

use tempdb
CREATE TABLE CLOCKING_DETAILS(emp_id int,clocking_date datetime default getdate(),c_date smalldatetime default getdate(),time_in datetime,time_out datetime)
CREATE TABLE EMPLOYEES(emp_id int,empname varchar(30),active int)
insert into EMPLOYEES
SELECT 12,'Bob',1 UNION ALL
SELECT 13,'Jeff',1 UNION ALL
SELECT 14,'Tom',0
INSERT INTO CLOCKING_DETAILS(emp_id,c_date,time_in,time_out)
SELECT 12, '2009-01-07','2009-01-07 06:21:42.000 ','2009-01-07 12:04:22.000' UNION ALL
SELECT 13, '2009-01-07','2009-01-07 06:21:42.000 ','2009-01-07 12:04:22.000' UNION ALL
SELECT 14, '2008-01-07','2008-01-07 06:21:42.000 ','2008-01-07 12:04:22.000' UNION ALL
SELECT 12, '2009-01-07','2009-01-07 12:51:57.000',NULL

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 14:28:29


SELECT e.empname,cd.c_date,cd.time_in,cd.time_out
FROM EMPLOYEES e
INNER JOIN CLOCKING_DETAILS cd
ON cd.emp_Id=e.emp_Id
WHERE e.active=1

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-05-18 : 03:31:54

vishak,

i dont know what you gave. I knew that much sql. the below is working for me.

SELECT cd.emp_id, e.first_name First_Name, max(e.last_name) Last_Name,
min(CASE WHEN (cd.time_out IS NULL) THEN 'in' ELSE 'out' END) AS cStatus
FROM dbo.CLOCKING_DETAILS AS cd INNER JOIN
dbo.EMPLOYEES AS e ON cd.emp_id = e.emp_id
WHERE (e.active = 1)
GROUP BY cd.emp_id, e.first_name
ORDER BY e.first_name

or

SELECT * FROM
(
select row_number() over
(partition by cd.emp_id order by time_in) AS RW,
cd.emp_id,
cd.clocking_date,
cd.time_in,cd.time_out,
CASE WHEN cd.time_out is NULL THEN 'IN' ELSE 'Out'End As cStatus
FROM dbo.CLOCKING_DETAILS cd
INNER JOIN dbo.EMPLOYEES e
ON cd.emp_id = e.emp_id
WHERE (e.active = '1')
)
myAlias WHERE RW = 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:33:11

i gave the sql for getting what you showed in beginning as sample o/p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources