select distinct rows from result

i write a program for time recorder for employees tracking , my SQL gets data from clock table were the data stored as follows :

employee_no      date      time      status
1      1/1/2008      08:00:00      1
1      1/1/2008      15:00:00      2
1      2/1/2008      08:00:00      1
1      2/1/2008      09:00:00      1
1      2/1/2008      15:00:00      2
1      2/1/2008      15:30:00      2

status 1 means employee entering work
status 2 means employee leaving work

i reached with my sql to get the result as this form by number of joins as follows:

employee_no      date      date_name      time_in      time_out
1      1/1/2008      satarday      08:00:00      15:00:00
1      2/1/2008      sunday      08:00:00      15:00:00
1      2/1/2008      sunday      08:00:00      15:30:00
1      2/1/2008      sunday      09:00:00      15:00:00
1      2/1/2008      sunday      09:00:00      15:30:00
1      3/1/2008      monday      08:00:00      15:00:00

the problem appears in my sql when the employee by wrong clock-in more than one time and so when he clock-out more the one ..

i need a suitable SQL to get the row which contains the first clock-in time and the last clock-out at that day as appears in the date 2/1/2008
SELECT     *
FROM       (SELECT     ddate as ddate2,*
                        FROM         Employees CROSS JOIN months
                        WHERE    Employees.EmpNo =:EmpNo1 AND DATEDIFF(month,:date11, months.ddate) = 0) bbb LEFT OUTER JOIN
                          (SELECT     TOP 100 PERCENT c.T_NO, c.T_DATE, c.T_DATE AS Expr1, c.T_TIME AS Enter_Time, c.T_DATA, c.T_STATUS, r.T_DATE AS Date2, 
                                                   r.T_TIME AS Leave_Time, r.T_DATA AS DATA2, r.T_STATUS AS STATUS2, DATEDIFF(minute, :EnterTime, c.T_TIME) AS lateEnter, 
                                                   DATEDIFF(minute,:LeaveTime, r.T_TIME) AS lateLeave, c.type
                             FROM         dbo.TerminalsData c LEFT OUTER JOIN
                                                   dbo.TerminalsData r ON c.T_DATA = r.T_DATA AND c.T_STATUS = 1 AND r.T_STATUS = 2 AND DATEDIFF(d, c.T_DATE, r.T_DATE) 
                                                   = 0
                             WHERE     (c.T_NO = 1) AND (c.T_STATUS = 1) AND (c.T_DATA =:EmpNo2) AND (DATEDIFF(month, :date12, c.T_DATE) = 0)) DERIVEDTBL ON 
                      bbb.EmpNo = DERIVEDTBL.T_DATA AND DATEDIFF(dd, bbb.ddate, DERIVEDTBL.T_DATE) = 0
ORDER BY bbb.ddate

Open in new window

tareqa1Asked:
Who is Participating?
 
HuyBDConnect With a Mentor Commented:
try this
select employee_no,date,Datepart(weekday,date), 
MIN(case status when 1 then time end) as time_in,
MIN(case status when 2 then time end) as time_out
from Employees
group by employee_no,date

Open in new window

0
 
elimesikaCommented:
HI

I guess that HuyBD means
select employee_no,date,Datepart(weekday,date), 
MIN(case status when 1 then time end) as time_in,
MAX(case status when 2 then time end) as time_out
from Employees
group by employee_no,date

Open in new window

0
 
HuyBDCommented:
ohh yes, thank elimesika, change min to max function in line 3
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
tareqa1Author Commented:
thank you very much HuyBD , your idea worked successfully ,
you got the points
but i have another related question,

we have another situation when the employee go-out and back during the work time
he must clock-out-for-job   and   clock-in-from-job

clock-in = 1
clock-out = 2
clock-out-for-job = 3
clock-in-from-job = 4

the problem with my sql is when the employee go-out and back more than one time , my join query
results multiple rows , the resulted rows should be the first out with the first back and the second out with the second back .. and so on ..

employee_no      date      time      status
1      1/1/2008      10:00:00      3
1      1/1/2008      11:00:00      4
1      1/1/2008      11:30:00      3
1      1/1/2008      12:40:00      4
1      1/1/2008      13:00:00      3
1      1/1/2008      15:30:00      4

the result should be as follows:

employee_no      date      date_name      job_out      job-in
1      1/1/2008      satarday      10:00:00      11:00:00
1      1/1/2008      sunday      11:30:00      12:40:00
1      2/1/2008      sunday      13:00:00      15:30:00

if you have a solution i will re ask this problem in another question with another 500 point :)

thanks again  HuyBD
0
 
HuyBDCommented:
I dont kown whether data's inserted when employee go-out and back ,
If not, data may look like

1      1/1/2008      10:00:00      3

1      1/1/2008      11:30:00      3
1      1/1/2008      12:40:00      4
1      1/1/2008      13:00:00      3
1      1/1/2008      15:30:00      4
0
 
HuyBDCommented:
I dont testing with the below query, could you try it
select employee_no,date,Datepart(weekday,date), 
MIN(case status when 3 then time end) as job_out,
MAX(case status when 4 then time end) as job_in
from Employees
group by employee_no,date,status*2

Open in new window

0
 
tareqa1Author Commented:
i want the result be as this:

employee_no      date      date_name      job_out      job-in
1      1/1/2008      satarday    10:00:00      11:00:00
1      1/1/2008      satarday    11:30:00      12:40:00


but your idea, please read the sql i attached bellow, the results are:

employee_no      date      date_name      job_out      job-in
1      1/1/2008      satarday    10:00:00      12:00:00

SELECT     tr.DATE , MIN(tr.TIME) AS job_out,MAX(tl.TIME) AS job_in
FROM         Data tr INNER JOIN Data tl 
	     ON tr.T_DATA = tl.T_DATA
WHERE     (tr.Employee_no = '1000800') AND (tr.STATUS = 3) AND (tl.STATUS = 4) AND (DATEDIFF(d, tr.DATE, tl.DATE) = 0)
GROUP BY tr.Employee_no, tr.DATE, tr.STATUS *2

Open in new window

0
 
HuyBDCommented:
ok, try below query
Hope this help

HuyBD
select employee_no,date,Datepart(weekday,date),time as job_out
(select time from Employees t where t.date=Employees.date
and t.time>Employees.time and status=4 order by time limit 1) as job_in
from Employees
where status=3

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.