Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks
select
Employee,
datediff(mi, min(TransactionDate),max(TransactionDate)) as MinSpent
from
YourTable
group by
Employee
order by
Employee
create table #table
(
ID int,
Employee char(4),
TransactionDate datetime
)
insert into #table
select 1, 'EmpA', '2010-02-01 20:28:40' union all
select 2, 'EmpA', '2010-02-01 20:51:08' union all
select 3, 'EmpA', '2010-02-01 21:25:08' union all
select 4, 'EmpC', '2010-02-03 20:48:15' union all
select 5, 'EmpC', '2010-02-03 20:50:36' union all
select 6, 'EmpC', '2010-02-03 20:50:36' union all
select 7, 'EmpA', '2010-02-03 20:53:47' union all
select 8, 'EmpF', '2010-02-03 22:08:55' union all
select 9, 'EmpF', '2010-02-03 22:14:13' union all
select 10, 'EmpA', '2010-02-03 22:24:58' union all
select 11, 'EmpA', '2010-02-03 22:24:58' union all
select 12, 'EmpA', '2010-02-03 23:44:56' union all
select 13, 'EmpA', '2010-02-03 23:48:02' union all
select 14, 'EmpA', '2010-02-03 23:49:32' union all
select 15, 'EmpA', '2010-02-04 02:06:55' union all
select 16, 'EmpA', '2010-02-04 02:06:55' union all
select 17, 'EmpA', '2010-02-04 17:45:00'
-- Query starts here --------------------------------------------------
declare @table table
(
unique_id int identity(1,1), -- to generate unique no to loop through
rank_no int, -- this is differenciate each group of same employee
ID int,
Employee char(4),
TransactionDate datetime
)
insert into @table (ID, Employee, TransactionDate)
select * from #table
declare @min int, @max int, @rankno int
declare @emp varchar(10), @cur_emp varchar(10)
set @min = 1
set @emp = ''
set @cur_emp = ''
set @rankno = 1
select @max = max(unique_id) from @table
while @min <= @max
begin
select @emp = Employee from @table where unique_id = @min
if @cur_emp = ''
set @cur_emp = @emp
if @cur_emp <> @emp
begin
set @cur_emp = @emp
set @rankno = @rankno + 1
end
update @table set rank_no = @rankno where unique_id = @min
set @min = @min + 1
end
select Employee, sum(datediff(mi, minTransactionDate, maxTransactionDate)) as MinSpent
from
(
select rank_no, Employee, min(TransactionDate) minTransactionDate, max(TransactionDate) maxTransactionDate
from @table
group by rank_no, Employee
) a
group by Employee
-- Query ends here --------------------------------------------------
Employee MinSpent
EmpA 1218
EmpC 2
EmpF 6
Hope I have understood your requirement correctly
create table emp_time (id int, employee varchar(20), trandate datetime)
insert into emp_time
select
1,'EmpA','2010-02-01 20:28:40'
UNION
SELECT
2,'EmpA','2010-02-01 20:51:08'
UNION
SELECT
3,'EmpA','2010-02-01 21:25:08'
UNION
SELECT
4,'EmpC','2010-02-03 20:48:15'
UNION
SELECT
5,'EmpC','2010-02-03 20:50:36'
UNION
SELECT
6,'EmpC','2010-02-03 20:50:36'
UNION
SELECT
7,'EmpA','2010-02-03 20:53:47'
UNION
SELECT
8,'EmpF','2010-02-03 22:08:55'
UNION
SELECT
9,'EmpF','2010-02-03 22:14:13'
UNION
SELECT
10,'EmpA','2010-02-03 22:24:58'
UNION
SELECT
11,'EmpA','2010-02-03 22:24:58'
UNION
SELECT
12,'EmpA','2010-02-03 23:44:56'
UNION
SELECT
13,'EmpA','2010-02-03 23:48:02'
UNION
SELECT
14,'EmpA','2010-02-03 23:49:32'
UNION
SELECT
15,'EmpA','2010-02-04 02:06:55'
UNION
SELECT
16,'EmpA','2010-02-04 02:06:55'
UNION
SELECT
17,'EmpA','2010-02-04 17:45:00'
select
total_min.employee,
sum(min_spent) total_min
from
(
select
employee,
anchorID,
datediff(mi,min(trandate),max(trandate)) min_spent
from
(
select
e.* ,
coalesce((select min(id) from emp_time where employee<>e.employee and id>e.id), (select max(id) from emp_time)) anchorID
from
emp_time e
) pass
group by
employee,
anchorID
) total_min
group by
total_min.employee
--Result:
employee total_min
-------------------- -----------
EmpA 1218
EmpC 2
EmpF 6
;with cte as (
select employee,transactiondate
,convert(char(8),transactiondate,112) as Thedate
,row_number() over (partition by convert(char(8),transactiondate,112)
order by transaction date) as rn
from yourtable
)
, cte1 as (
select datediff(mi,a.transactiondate,b.transactiondate) as tm
,b.employee
from cte as a
inner join cte as b
on a.thedate=b.thedate
and a.rn<b.rn
where not exists (select employee from cte as x
where x.thedate=a.thedate
and x.employee=a.employee
and x.rn = a.rn-1
)
and not exists (select employee from cte as x
where x.thedate=a.thedate
and x.rn between a.rn and b.rn - 1
and x.employee <> a.employee
)
and not exists (select employee from cte as x
where x.thedate=a.thedate
and x.rn=b.rn+1
and x.employee=a.employee
and a.employee=b.employee
)
)
select employee,sum(tm) as duration
from cte1
group by employee
order by 1
select
total_min.employee,
sum(min_idle) as total_min_idle,
sum(min_spent) as total_min_work,
sum(min_idle)+
sum(min_spent) as total_min_spent
from
(
select
employee,
anchorID,
datediff(mi,min(trandate),max(trandate)) min_spent,
min(idle) as min_idle
from
(
select
e.* ,
coalesce((select min(id) from emp_time where employee<>e.employee and id>e.id), (select max(id) from emp_time)) anchorID,
datediff(mi,coalesce((select max(trandate) from emp_time where employee<>e.employee and id<e.id), (select min(trandate) from emp_time)),trandate) as idle
from
emp_time e
) pass
group by
employee,
anchorID
) total_min
group by
total_min.employee
select
total_min.employee,
sum(min_idle) as total_min_idle,
sum(min_spent) as total_min_work,
sum(min_idle)+
sum(min_spent) as total_min_spent
from
(
select
employee,
anchorID,
datediff(mi,min(trandate),max(trandate)) min_spent,
min(datediff(mi,assign_time,trandate)) as min_idle
from
(
select
e.* ,
coalesce((select min(id) from emp_time where employee<>e.employee and id>e.id), (select max(id) from emp_time)) anchorID,
coalesce((select max(trandate) from emp_time where employee<>e.employee and id<e.id), (select min(trandate) from emp_time)) as assign_time
from
emp_time e
) pass
group by
employee,
anchorID
) total_min
group by
total_min.employee
-- ###############################################
-- generate the Emp_Time_StrippedBad table and data
create table Emp_Time_Strippedbad
(
ID int,
Employee char(4),
TransactionDate datetime
)
insert into Emp_Time_StrippedBad
select 1, 'EmpA', '2010-02-01 20:28:40' union all
--select 2, 'EmpA', '2010-02-01 20:51:08' union all
select 2, 'EmpA', '2010-02-01 21:25:08' union all
select 3, 'EmpC', '2010-02-03 20:48:15' union all
--select 5, 'EmpC', '2010-02-03 20:50:36' union all
select 4, 'EmpC', '2010-02-03 20:50:36' union all
select 5, 'EmpA', '2010-02-03 20:53:47' union all
select 6, 'EmpF', '2010-02-03 22:08:55' union all
select 7, 'EmpF', '2010-02-03 22:14:13' union all
select 8, 'EmpA', '2010-02-03 22:24:58' union all
--select 11, 'EmpA', '2010-02-03 22:24:58' union all
--select 12, 'EmpA', '2010-02-03 23:44:56' union all
--select 13, 'EmpA', '2010-02-03 23:48:02' union all
--select 14, 'EmpA', '2010-02-03 23:49:32' union all
--select 15, 'EmpA', '2010-02-04 02:06:55' union all
--select 16, 'EmpA', '2010-02-04 02:06:55' union all
select 9, 'EmpA', '2010-02-04 17:45:00'
-- ###############################################
-- adapted query for Emp_Time_StrippedBad table
select
total_min.employee,
sum(min_idle) as total_min_idle,
sum(min_spent) as total_min_work,
sum(min_idle)+
sum(min_spent) as total_min_spent
from
(
select
employee,
anchorID,
datediff(mi,min(TransactionDate),max(TransactionDate)) min_spent,
min(datediff(mi,assign_time,TransactionDate)) as min_idle
from
(
select
e.* ,
coalesce((select min(id) from Emp_Time_Strippedbad where employee<>e.employee and id>e.id), (select max(id) from Emp_Time_Strippedbad)) anchorID,
coalesce((select max(TransactionDate) from Emp_Time_Strippedbad where employee<>e.employee and id<e.id), (select min(TransactionDate) from Emp_Time_Strippedbad)) as assign_time
from
Emp_Time_Strippedbad e
) pass
group by
employee,
anchorID
) total_min
group by
total_min.employee
Results:
employee total_min_idle total_min_work total_min_spent
-------- -------------- -------------- ---------------
EmpA 13 1218 1231
EmpC 2843 2 2845
EmpF 75 6 81
select
total_min.employee,
sum(min_idle) as total_min_idle,
sum(min_worked) as total_min_work,
sum(min_idle)+
sum(min_worked) as total_min_spent
from
(
select
employee,
anchorID,
datediff(mi,min(TransactionDate),max(TransactionDate)) min_worked,
min(datediff(mi,assign_time,TransactionDate)) as min_idle
from
(
select
e.* ,
coalesce((select min(id) from Emp_Time_Strippedbad where employee<>e.employee and id>e.id), (select max(id) from Emp_Time_Strippedbad)) anchorID,
coalesce((select max(TransactionDate) from Emp_Time_Strippedbad where employee<>e.employee and id<e.id), (select min(TransactionDate) from Emp_Time_Strippedbad)) as assign_time
from
Emp_Time_Strippedbad e
) pass
group by
employee,
anchorID
) total_min
group by
total_min.employee
7 EmpA 2010-02-03 20:53:47
EmpA seems to resume his work, but there is no end time next to this record ? EmpF starts next
Raj