First event of day - 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 2000 Forums

SQL Server Development (2000)

First event of day

Author

Topic

hmantri1
Starting Member

8 Posts

Posted - 2004-12-04 : 06:17:24

Hi,

I have a table which has a column named EVENTIME and everytime a person swipes his card in a reader the time is recorded in the eventime column. The problem is that a person may swipe the card throught the reader more than once in a day. I want to get the First time the person swipes his card through the reader. I tried using the MIN DISTINCT function but it returns the first time ever the person swiped through the reader which is months away. I want the first time everyday the person swipes. IS there a way to do this . The other columns in the table are firstname, lastname, midname, title, department, employee#. I tried using DISTINCT in the starting of the select statement but it doesnt do anything. The table has around 8000 rows but sometimes the names are repeated.Actually it should only show 49 rows because there are only 49 employees in the department.

Thanks for the help.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-04 : 10:37:45

select *
from tbl t1
where EVENTIME = (select min(t2.EVENTIME) from tbl t2 where t1.employee# = t2.employee#)

or
select t1.*
from tbl t1
join
(select employee#, EVENTIME from tbl t2 group by employee#) t2
on t1.EVENTIME = t2.EVENTIME
and t1.employee# = t2.employee#

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-04 : 10:41:41

Or:

SELECT Employee#, DateAdd(day, DateDiff(day, 0, eventime), 0) AS Day, Min(eventime)
FROM myTable
GROUP BY Employee#, DateAdd(day, DateDiff(day, 0, eventime), 0)

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-12-04 : 10:42:50

select employee#,min(EVENTIME)
from tbl
group by employee#,dateadd(d,datediff(d,0,EVENTIME),0)

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-04 : 18:40:53

though i recommended the query in the below post, i like VIG's query

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-04 : 19:09:15

try this
select empid,min(eventtime)
from table1
group by empid,
datepart(year,eventtime),datepart(month,eventtime),datepart(day,eventtime)

hmantri1
Starting Member

8 Posts

Posted - 2004-12-05 : 04:30:48

quote:
Originally posted by arpp

try this
select empid,min(eventtime)
from table1
group by empid,
datepart(year,eventtime),datepart(month,eventtime),datepart(day,eventtime)

Thank you every one for your helpful hints, i tried the above query and it works thank you once again.
Now i have another problem though. The problem is that now i have 2 separate views, one that records the first time the employee swiped his card and another one that records the last time the employee swiped his card and left. I want to combine both these columns in a single view. The problem is that when i join them with this statement

SELECT T1.EMPLOYEE#,T1.FIRSTNAME,T1.LASTNAME,T1.Department,T1.Title, T1.TIME_IN,T2.TIME_OUT
FROM TIME_IN_FIRST T1,TIME_OUT__LAST T2
WHERE T1.EMPLOYEE# = T2.EMPLOYEE#

It generated a million rows, although the TIME_IN_FIRST has 6295 rows and TIME_OUT_LAST has 6330 rows, so it should only generate 6330 rows. What it is doing is that because T1 has less rows , so it taking the time and putting the same time for each row when put in the same view as TIME_OUT_LAST. Any suggestions on what i should do . Should i use a RIGHT OUTER JOIN /FULL OUTER JOIN instead of this INNER JOIN.

Please any suggestions are really helpful.

Thanks once again

hmantri1
Starting Member

8 Posts

Posted - 2004-12-05 : 04:34:23

quote:
Originally posted by arpp

try this
select empid,min(eventtime)
from table1
group by empid,
datepart(year,eventtime),datepart(month,eventtime),datepart(day,eventtime)

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-05 : 10:58:24

Try this
SELECT EMPLOYEE#,FIRSTNAME,LASTNAME,Department,Title, TIME_IN as Time_Swiped
FROM TIME_IN_FIRST
UNION
SELECT EMPLOYEE#,FIRSTNAME,LASTNAME,Department,Title, TIME_OUT_LAST as Time_Swiped
FROM TIME_OUT

hmantri1
Starting Member

8 Posts

Posted - 2004-12-05 : 14:02:17

quote:
Originally posted by arpp

Try this
SELECT EMPLOYEE#,FIRSTNAME,LASTNAME,Department,Title, TIME_IN as Time_Swiped
FROM TIME_IN_FIRST
UNION
SELECT EMPLOYEE#,FIRSTNAME,LASTNAME,Department,Title, TIME_OUT_LAST as Time_Swiped
FROM TIME_OUT

No this doesnt work, although i will try it with the real data again tomorrow, but it only combines the values from the tables and displays them in the TIME_IN column. Also i just want it to show null if that day the person did not leave only, like if he entered on 5th December his record is there in the TIME_IN column but he did not leave through the monitored doors, so there isnt a record for him that day that he left, that should show as NULL and vice versa.

Thanks all for ur kind help.

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-05 : 14:49:53

Try out this query...

SELECT

(CASE
WHEN A.EMPLOYEE# IS NULL THEN B.EMPLOYEE#
ELSE A.EMPLOYEE#
END) AS EMPLOYEE#, A.TIME_IN,B.TIME_OUT

FROM TIME_IN_FIRST A FULL OUTER JOIN TIME_OUT_LAST B

ON A.EMPLOYEE#=B.EMPLOYEE#

hmantri1
Starting Member

8 Posts

Posted - 2004-12-06 : 08:11:38

quote:
Originally posted by arpp

Try out this query...

SELECT

(CASE
WHEN A.EMPLOYEE# IS NULL THEN B.EMPLOYEE#
ELSE A.EMPLOYEE#
END) AS EMPLOYEE#, A.TIME_IN,B.TIME_OUT

FROM TIME_IN_FIRST A FULL OUTER JOIN TIME_OUT_LAST B

ON A.EMPLOYEE#=B.EMPLOYEE#

Hi dude,

This doesnt seem to solve the problem.
Here is a sample output

This is the TIME_IN_FIRST view


EMPNO    FNAME  MNAME  LNAME  DEPT  Title       TIME_IN
80001109 AAA	BBB    CCC    HR    Admin. Emp.	2004-12-06 10:55:26
80001051 DDD	EEE    FFF    HR    Registrar	2004-12-06 10:51:07
80001014 H**    A**    A**    HR    Translator 	2004-12-06 09:54:24

This is the TIME_OUT_LAST view


EMPNO    FNAME  MNAME  LNAME  DEPT  Title       TIME_OUT
80001109 AAA	BBB    CCC    HR    Admin. Emp.	2004-12-06 12:12:31
80001051 DDD	EEE    FFF    HR    Registrar	2004-12-06 12:04:53
80001014 H**    A**    A**    HR    Translator 	2004-12-06 11:53:46
80001135 G**    F**    D**    HR    Tech.Supp.  2004-12-06 08:15:02

Now when i do the join condition on the empno, what it does is that since there are more columns in the TIME_OUT_LAST view, it takes the same time_out value for each and every row of TIME_IN_FIRST, what i think it is doing is a cross join or something like that....
THe union query does work that arpp pointed out before, but i want to separe columns like

EMPNO FNAME MNAME LNAME DEPT Title TIME_IN TIME_OUT

but for some reason the join condition on empno doesnt work.

Also the null thing doesnt work. I want it to show null in the output, because sometimes a person may come in through a reader 2, but he go out through some door which is not monitored....so in that case i want it to show null in the output.

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-06 : 18:46:16

I gave the example only for EMPID.I created your sample and it just worked fine.I didn't consider your other fields (as solution for EMPID can be extended to your other fields too). Infact the query I posted would take care of both TIME_IN and TIME_OUT.It'll return a row if either TIME_IN or TIME_OUT is present in the database. I'll post the result in about half hour.

arpp
Yak Posting Veteran

61 Posts

Posted - 2004-12-06 : 19:08:12

Here is the complete query query

SELECT
(CASE
WHEN A.EMPID IS NULL THEN B.EMPID
ELSE A.EMPID
END) AS EMPID,
(CASE
WHEN A.FNAME IS NULL THEN B.FNAME
ELSE A.FNAME
END) AS FNAME,
(CASE
WHEN A.LNAME IS NULL THEN B.LNAME
ELSE A.LNAME
END) AS LNAME,
MIN(A.SWIPE) AS TIME_IN,MAX(B.SWIPE) AS TIME_OUT
FROM TBLSWIPE A FULL OUTER JOIN TBLSWIPEOUT B
ON A.EMPID=B.EMPID
GROUP BY
(CASE
WHEN A.EMPID IS NULL THEN B.EMPID
ELSE A.EMPID
END),
(CASE
WHEN A.FNAME IS NULL THEN B.FNAME
ELSE A.FNAME
END) ,
(CASE
WHEN A.LNAME IS NULL THEN B.LNAME
ELSE A.LNAME
END)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-06 : 19:53:40

is there a reason everyone is trying to make this so complicated?

all you need to do is:

select empid,min(eventtime) as StartTime, max(eventtime) as EndTime
from table1
group by empid,
datepart(year,eventtime),datepart(month,eventtime),datepart(day,eventtime)

- Jeff

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-06 : 21:47:08

select top 1 empid,eventime
from tableName
group by empid,eventime
having datediff(day,eventime,getdate())=0
and empid=@empid

--------------------
keeping it simple...

AlDragon
Starting Member

12 Posts

Posted - 2004-12-06 : 21:50:51

test

Al Franzini

AlDragon
Starting Member

12 Posts

Posted - 2004-12-06 : 21:54:56

I imported the time in and time out results to their own tables and ran this query using the right join. Is this what your looking for?

Select t1.fname,t1.mname,t1.lname,t1.dept,t1.title,t1.time_in,t2.time_out
from empin t1
right join empout t2
on t1.empno = t2.empno

Results:
First middle last Dept title Time_in Time_out
AAA BBB CCC HR Admin. 12/6/2004 10:55 12/6/2004 12:12
DDD EEE FFF HR Registrar 12/6/2004 10:51 12/6/2004 12:04
H** A** A** HR Translator 12/6/2004 9:54 12/6/2004 11:53
NULL NULL NULL NULL NULL NULL 12/6/2004 8:15

Al Franzini

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-12-06 : 22:00:52

quote:
Originally posted by jsmith8858

is there a reason everyone is trying to make this so complicated?

They have large credit card debit?

DavidM

"Always pre-heat the oven"

hmantri1
Starting Member

8 Posts

Posted - 2004-12-07 : 01:30:43

quote:
Originally posted by jsmith8858

is there a reason everyone is trying to make this so complicated?

all you need to do is:

select empid,min(eventtime) as StartTime, max(eventtime) as EndTime
from table1
group by empid,
datepart(year,eventtime),datepart(month,eventtime),datepart(day,eventtime)

- Jeff

This query is wrong as it only gets the TIME_OUT from the same TABLE 1, where as there are 2 tables, one for TIME_IN and one for TIME_OUT. Another thing is that, the query with the join on empid works only if you have similar number of rows in both tables, and so this query works fine, but when you have different number of rows in both tables here is what the output is


80001141	2004-06-16 08:15:55.000	2004-12-06 12:12:31.000
80001141	2004-02-23 08:06:25.000	2004-12-06 12:12:31.000
80001141	2004-06-23 08:18:42.000	2004-12-06 12:12:31.000
80001141	2004-05-23 08:14:29.000	2004-12-06 12:12:31.000
80001141	2004-03-06 08:05:22.000	2004-12-06 12:12:31.000
80001141	2004-06-09 07:54:58.000	2004-12-06 12:12:31.000
80001141	2004-11-27 08:11:50.000	2004-12-06 12:12:31.000
80001141	2004-04-11 09:01:14.000	2004-12-06 12:12:31.000
80001141	2004-05-29 07:46:39.000	2004-12-06 12:12:31.000
80001141	2004-02-22 08:10:31.000	2004-12-06 12:12:31.000
80001141	2004-05-04 08:01:07.000	2004-12-06 12:12:31.000
80001141	2004-06-02 07:59:09.000	2004-12-06 12:12:31.000
80001141	2004-05-15 08:04:23.000	2004-12-06 12:12:31.000
80001141	2004-11-01 09:06:34.000	2004-12-06 12:12:31.000
80001141	2004-06-13 08:23:45.000	2004-12-06 12:12:31.000
80001141	2004-09-11 08:21:24.000	2004-12-06 12:12:31.000
80001141	2004-06-26 08:17:21.000	2004-12-06 12:12:31.000
80001141	2004-10-11 08:08:58.000	2004-12-06 12:12:31.000
80001141	2004-09-13 08:41:24.000	2004-12-06 12:12:31.000
80001141	2004-11-09 09:14:29.000	2004-12-06 12:12:31.000
80001141	2004-03-07 07:59:54.000	2004-12-06 12:12:31.000
80001141	2004-03-17 08:04:36.000	2004-12-06 12:12:31.000
80001141	2004-02-11 08:57:14.000	2004-12-06 12:12:31.000
80001141	2004-04-26 12:50:03.000	2004-12-06 12:12:31.000
80001141	2004-05-30 08:29:10.000	2004-12-06 12:12:31.000
80001141	2004-10-04 08:14:43.000	2004-12-06 12:12:31.000
80001141	2004-06-20 07:56:10.000	2004-12-06 12:12:31.000
80001141	2004-08-14 08:19:58.000	2004-12-06 12:12:31.000
80001141	2004-09-26 08:56:35.000	2004-12-06 12:12:31.000
80001141	2004-09-28 08:15:01.000	2004-12-06 12:12:31.000
80001141	2004-02-28 07:52:37.000	2004-12-06 12:12:31.000
80001141	2004-03-24 08:50:53.000	2004-12-06 12:12:31.000
80001141	2004-11-29 08:07:37.000	2004-12-06 12:12:31.000
80001141	2004-03-16 08:02:45.000	2004-12-06 12:12:31.000
80001141	2004-05-26 08:10:29.000	2004-12-06 12:12:31.000
80001011	2004-10-09 07:47:45.000	2004-12-06 08:15:02.000
80001087	2004-06-16 08:00:37.000	2004-12-06 08:15:02.000
80001106	2004-10-04 08:10:24.000	2004-12-06 08:15:02.000
80001011	2004-09-21 08:04:53.000	2004-12-06 08:15:02.000
80001087	2004-07-21 08:00:06.000	2004-12-06 08:15:02.000
80001130	2004-04-26 08:18:04.000	2004-12-06 08:15:02.000
80001123	2004-04-25 07:55:35.000	2004-12-06 08:15:02.000
80001130	2004-05-23 08:16:11.000	2004-12-06 08:15:02.000
80001130	2004-06-06 08:09:23.000	2004-12-06 08:15:02.000
80001087	2004-05-22 08:03:13.000	2004-12-06 08:15:02.000
80001011	2004-06-21 08:00:31.000	2004-12-06 08:15:02.000
80001123	2004-05-08 08:00:56.000	2004-12-06 08:15:02.000
80001130	2004-09-27 08:14:17.000	2004-12-06 08:15:02.000
80001011	2004-05-30 08:14:48.000	2004-12-06 08:15:02.000
80001106	2004-07-20 08:00:48.000	2004-12-06 08:15:02.000
80001123	2004-06-14 07:57:12.000	2004-12-06 08:15:02.000
80001130	2004-07-28 08:18:26.000	2004-12-06 08:15:02.000
80001130	2004-10-19 09:07:19.000	2004-12-06 08:15:02.000
80001011	2004-04-27 07:55:43.000	2004-12-06 08:15:02.000
80001123	2004-05-11 07:55:12.000	2004-12-06 08:15:02.000
80001011	2004-10-25 08:17:09.000	2004-12-06 08:15:02.000
80001106	2004-05-05 08:02:34.000	2004-12-06 08:15:02.000
80001106	2004-10-31 08:56:02.000	2004-12-06 08:15:02.000

This is what i am talking about. I will post another part of both the tables so that you'll have something concrete to work on ..
Thanks again for your help.

hmantri1
Starting Member

8 Posts

Posted - 2004-12-07 : 01:44:59

Here is a part of the TIME_IN


EMPNO    DEPT              Title             Time_IN     
90001098	Academic  Programmes	Assistant Professor	2004-12-07 09:00:41.000
80001048	IT	Multimedia Specialist	2004-12-07 08:37:09.000
90001156	Academic  Programmes	Assistant Professor	2004-12-07 08:36:55.000
90001103	Academic  Programmes	Assistant Professor	2004-12-07 08:32:40.000
80001177	IT	IT Director	2004-12-07 08:26:15.000
90001043	Academic  Programmes	Assistant Professor	2004-12-07 08:24:49.000
80001130	Admission Department	Secretary	2004-12-07 08:23:50.000
80001168	General Admin & Fin. Affairs	Marketing & P.R. Officer	2004-12-07 08:22:53.000
80001084	University President Office	Executive Secretary	2004-12-07 08:17:37.000
80001149	Academic Support Services	Teaching Assistant	2004-12-07 08:17:03.000
80001139	General Admin & Fin. Affairs	Assistant Administrative Officer	2004-12-07 08:16:28.000
80001021	General Admin & Fin. Affairs	Accountant	2004-12-07 08:15:26.000
80001106	Admission Department	Secretary	2004-12-07 08:13:32.000
80001025	Admission Department	Admin. Employees	2004-12-07 08:13:30.000
80001172	General Admin & Fin. Affairs	Trainee	2004-12-07 08:12:12.000
80001141	General Admin & Fin. Affairs	Admin. Officer	2004-12-07 08:11:17.000
80001135	Admission Department	Receptionist	2004-12-07 08:09:09.000
80001059	Admission Department	Social Researcher	2004-12-07 08:08:46.000
80001054	Admission Department	Registrar	2004-12-07 08:08:39.000
80001012	Admission Department	Registrar	2004-12-07 08:08:38.000
80001004	University President Office	Executive Secretary	2004-12-07 08:07:59.000
80001032	General Admin & Fin. Affairs	Worker	2004-12-07 08:07:37.000
80001138	General Admin & Fin. Affairs	Admin. Officer (Recruitment)	2004-12-07 08:07:31.000
80001150	Academic Support Services	Teaching Assistant	2004-12-07 08:07:28.000
80001131	General Admin & Fin. Affairs	Admin. Employees	2004-12-07 08:07:25.000
80001011	General Admin & Fin. Affairs	Secretary	2004-12-07 08:07:22.000
80001051	Admission Department	Senior Officer (Registaration)	2004-12-07 08:07:12.000
80001169	General Admin & Fin. Affairs	Security Guard	2004-12-07 08:07:03.000
80001175	General Admin & Fin. Affairs	Senior Accountant	2004-12-07 08:05:40.000
80001028	IT	Computer Technician	2004-12-07 08:05:27.000
80001127	University President Office	Administrator Assistant	2004-12-07 08:04:54.000
80001050	General Admin & Fin. Affairs	Public Relation Research	2004-12-07 08:04:45.000
80001005	General Admin & Fin. Affairs	Worker	2004-12-07 08:03:59.000
90001113	Academic  Programmes	Assistant Professor	2004-12-07 08:03:47.000
80001123	General Admin & Fin. Affairs	Secretary	2004-12-07 08:02:11.000
80001087	General Admin & Fin. Affairs	Secretary	2004-12-07 08:02:08.000

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources