May 16, 2009 at 6:35 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
= = = 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 6:08 am
here's some consumable data to help look at the problem.
i think in your example you said one column was c_date, but int he SQL you referenced clocking_date; i added both fo the example.
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
i don't think you should be getting the min and the max for the fields, especially since the table explicitly has in and out fields.
it looks like you are trying to find ONE record per employee(the last entered?) is that right?
since you are using 2005, i would use the row_number function to get the data instead:
check out the intermediate results:
select row_number() over (partition by CLOCKING_DETAILS.emp_id order by time_in) AS RW,*
FROM dbo.CLOCKING_DETAILS
INNER JOIN dbo.EMPLOYEES
ON dbo.CLOCKING_DETAILS.emp_id = dbo.EMPLOYEES.emp_id
WHERE (dbo.EMPLOYEES.active = 1
and i would wrap THAT in a query to get just the columns where my alisaed rownumber RW = 1:
SELECT * FROM (
select row_number() over (partition by CLOCKING_DETAILS.emp_id order by time_in) AS RW,*
FROM dbo.CLOCKING_DETAILS
INNER JOIN dbo.EMPLOYEES
ON dbo.CLOCKING_DETAILS.emp_id = dbo.EMPLOYEES.emp_id
WHERE (dbo.EMPLOYEES.active = 1
)MyAlias WHER RW = 1
Lowell
May 17, 2009 at 11:46 am
Lowell,
You are right on target. Also, I wanted to thank you for answering me and helping me with the code. truly appreciated. I am getting following error when I execute this sql
SELECT * FROM
(
select row_number() over
(partition by cd.emp_id order by time_in) AS RW,*
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
Msg 8156, Level 16, State 1, Line 1
The column 'emp_id' was specified multiple times for 'myAlias'.
May 17, 2009 at 12:18 pm
it's the * for the column list.
there's no problem running that statement all by itself, but if ytry to create a VIEW of it, you'll have problems because the emp_id exists twice; once in employee and another time in the clocking_details.
try this, where the * is preceded by only a single table instead:
SELECT * FROM
(
select row_number() over
(partition by cd.emp_id order by time_in) AS RW,
cd.*
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
Lowell
May 17, 2009 at 12:36 pm
The new sql statement works and gives me records. However, I do not need all the columns. I need to add a column cStatus to display In/Out based on the time_in or time_out column.
May 17, 2009 at 12:43 pm
change the cd.* to a list of the actual columns desired.
your status, I'm assuming is just one more field based on a case statement; you were not explicit on the expected values, so her's my WAG:
SELECT * FROM
(
select row_number() over
(partition by cd.emp_id order by time_in) AS RW,
cd.emp_id,
cd.c_date,
cd.time_in,cd.time_out,
CASE
WHEN cd.time_out is NULL
THEN 'Clocked IN'
ELSE 'Clocked 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
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply