Solved

select distinct rows from result

Posted on 2008-06-23
8
231 Views
Last Modified: 2010-03-20
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

0
Comment
Question by:tareqa1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 17

Accepted Solution

by:
HuyBD earned 500 total points
ID: 21853018
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
 
LVL 19

Expert Comment

by:elimesika
ID: 21853499
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 21853802
ohh yes, thank elimesika, change min to max function in line 3
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:tareqa1
ID: 21863502
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 21863971
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 21864150
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
 

Author Comment

by:tareqa1
ID: 21864407
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
 
LVL 17

Expert Comment

by:HuyBD
ID: 21875241
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

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question