Solved

select distinct rows from result

Posted on 2008-06-23
8
229 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

710 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