Solved

select distinct rows from result

Posted on 2008-06-23
8
221 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
 

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now