Solved

select distinct rows from result

Posted on 2008-06-23
8
222 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 32
Help Required 3 97
sql select record as one long string 21 24
Mysql Left Join Case 10 56
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

822 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