January 21, 2011 at 8:02 pm
Hi Guys,
Plz see below table..
EmpID WrkdTime
12 2010-09-01 10:06:00.000
12 2010-09-01 10:11:00.000
12 2010-09-01 10:16:00.000
12 2010-09-01 12:11:00.000
12 2010-09-02 10:00:00.000
12 2010-09-04 16:09:00.000
14 2010-09-01 10:11:00.000
14 2010-09-01 10:16:00.000
14 2010-09-01 14:10:00.000
14 2010-09-02 12:00:00.000
14 2010-09-03 16:00:00.000
Now the result should be:
EmpID StartTime Endtime Wrkd
12 2010-09-01 10:06:00.000 2010-09-01 12:11:00.000 2hours
12 2010-09-02 10:00:00.000 2010-09-04 16:09:00.000 2days 6hours
14 2010-09-01 10:11:00.000 2010-09-01 14:10:00.000 4 h0urs
14 2010-09-02 12:00:00.000 2010-09-03 16:00:00.000 1 day 4hours
Guys plz help me how to write logic for this..i am struggling a lot for this..I tried tis using CTE ..but i was not able to get ...plz guys help me..
Thanks in Advance..
January 21, 2011 at 11:42 pm
declare @datetime table
(EmpID int ,
WrkdTime datetime
);
insert into @datetime
select '12','2010-09-01 10:06:00.000' union all select '12','2010-09-01 10:11:00.000' union all
select '12','2010-09-01 10:16:00.000' union all select '12','2010-09-01 12:11:00.000' union all
select '12','2010-09-02 10:00:00.000' union all select '12','2010-09-04 16:09:00.000' union all
select '14','2010-09-01 10:11:00.000' union all select '14','2010-09-01 10:16:00.000' union all
select '14','2010-09-01 14:10:00.000' union all select '14','2010-09-02 12:00:00.000' union all
select '14','2010-09-03 16:00:00.000'
declare @datetime1 table
(EmpID int ,
WrkdTimemin datetime,
WrkdTimemax datetime,
Rowid int
);
insert into @datetime1
select distinct(empid),
min(WrkdTime)over (partition by convert(varchar, WrkdTime, 1),empid),
max(WrkdTime)over (partition by convert(varchar, WrkdTime, 1),empid) ,
ROW_NUMBER() OVER (ORDER BY EMPID ASC) AS ROWID
from @datetime
declare @datetime2 table
(EmpID int ,
WrkdTimemin datetime,
WrkdTimemax datetime
);
insert into @datetime2
select t1.empid,t1.WrkdTimemin,
case when t1.WrkdTimemin=t1.WrkdTimemax then
isnull((select t.WrkdTimemin from @datetime1 t where t.Rowid=t1.Rowid+1 and t.empid=t1.empid ),t1.WrkdTimemax)
else
t1.WrkdTimemax
end
from @datetime1 t1
select distinct(empid),WrkdTimemin as 'Start Time',WrkdTimemax as 'End Time',
STR(convert(int,datediff(hh,WrkdTimemin,WrkdTimemax)) / 24, 1) +'Days '+
STR(convert(int,datediff(hh,WrkdTimemin,WrkdTimemax)) % 24, 1)+'Hours '+
STR(convert(int,(datediff(hh,WrkdTimemin,WrkdTimemax) -
convert(int,datediff(hh,WrkdTimemin,WrkdTimemax))) * 60), 1)+'Seconds' as 'Worked'
from @datetime2 where WrkdTimemin<>WrkdTimemax and datediff(ms,WrkdTimemin,WrkdTimemax)>=0
Result
-----
122010-09-01 10:06:00.0002010-09-01 12:11:00.0000Days 2Hours 0Seconds
122010-09-02 10:00:00.0002010-09-04 16:09:00.0002Days 6Hours 0Seconds
142010-09-01 10:11:00.0002010-09-01 14:10:00.0000Days 4Hours 0Seconds
142010-09-02 12:00:00.0002010-09-03 16:00:00.0001Days 4Hours 0Seconds
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply