May 16, 2009 at 6:31 pm
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
May 17, 2009 at 12:15 am
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
May 17, 2009 at 4:27 am
Hello,
You may be able to simplify your query a little. Would it be true to say an Employee is “In” if they have a record in the CLOCKING_DETAILS Table with a Null value in the time_out Column, otherwise they are “Out”?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 8:59 am
No cross-posts. Lowell has already answered here:
http://www.sqlservercentral.com/Forums/Topic718578-149-1.aspx
May 17, 2009 at 9:08 am
Hello Florian,
May be you should have checked the timestamps on the posts ….
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
May 17, 2009 at 9:14 am
Hi John
Didn't compare the time-stamps. Just thought that Lowell's post might be the answer (I didn't validate it). It was an information for you and others.
Greets
Flo
May 17, 2009 at 11:38 am
i am so sorry for multiple posts. i wasnt sure where to put it. again very sorry.
and
thanks all for your contribution. I will sure to try all of them.
thanks
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply