Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

Can this be done in T-Sql?

Hello all,

I have the following table, ordered by transactionDate. Ordered files go against the grain, I know...
ID      Employee                          TransactionDate

1      EmpA            2010-02-01 20:28:40
2      EmpA            2010-02-01 20:51:08
3      EmpA            2010-02-01 21:25:08

4      EmpC            2010-02-03 20:48:15
5      EmpC            2010-02-03 20:50:36
6      EmpC            2010-02-03 20:50:36

7      EmpA            2010-02-03 20:53:47

8      EmpF            2010-02-03 22:08:55
9      EmpF            2010-02-03 22:14:13

10      EmpA            2010-02-03 22:24:58
11      EmpA            2010-02-03 22:24:58
12      EmpA            2010-02-03 23:44:56
13      EmpA            2010-02-03 23:48:02
14      EmpA            2010-02-03 23:49:32
15      EmpA            2010-02-04 02:06:55
16      EmpA            2010-02-04 02:06:55
17      EmpA            2010-02-04 17:45:00

What I need to do is find the minutes spent by each 'emp' emp A, C, and F. I thought I had the problem solved simply by Select max(transactionDate) - min(transactionDate) group by Employee but that's not the case. What they do is pass a 'ticket' back and forth between each other and work on the ticket for specified transaction dates. So, EmpA worked from 2010-02-01 20:28:40 to 2010-02-01 21:25:08, passed it empc, resumed at 2010-02-03 20:53:47, passed it to empC, resumed at 2010-02-03 22:24:58, and completed at 2010-02-04 17:45:00.

What I'm  trying to do is find the total times spent for employees a, c, and f. These times should sum up to datediff(mi, min(transactionDate), man(transactionDate)).  I know you can do it in a loop in c# or something, but can you do it in transact sql? I'm still pretty new to T-Sql.
Oh, to the inevitable 'Why would you want to do it in T-Sql" It's because we're trying to avoid the use of an executable program.
0
OutOnALimbAlways
Asked:
OutOnALimbAlways
  • 19
  • 11
  • 4
  • +3
1 Solution
 
Rajkumar GsSoftware EngineerCommented:
Can you explain this ?
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
0
 
ZberteocCommented:
Try this:
select 
	Employee,
	datediff(mi, min(TransactionDate),max(TransactionDate)) as MinSpent
from 
	YourTable 
group by
	Employee
order by 
	Employee

Open in new window

0
 
Rajkumar GsSoftware EngineerCommented:
I have created a temporary table to test my query. I have named my table as #table and inserted your sample data into this table
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'

Open in new window


If you want to test the query with this sample table data, execute the above query so that you have a temporary table and data.
Your requirement can be achieved with the help of loop. (right now, no other logic comes to my mind :))

Execute the below query at once, if you want to test with the temporary table #table
If you want to test with your data, search and replace '#table' with your actual tablename. and execute it.

-- 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 --------------------------------------------------

Open in new window


This is result I got
Employee	MinSpent
EmpA	1218
EmpC	2
EmpF	6

Open in new window

Hope I have understood your requirement correctly
0
 [eBook] Windows Nano Server

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

 
mmr159Commented:
I assume this table holds multiple tickets.  If this is the case, you will need to factor in a ticket id # or something.

Raj makes a good point about your data.  It looks like EmpA starts but does not end his work.

I'm thinking this problem will require a CTE, which I am not well-versed in so I cannot provide a solution.  However, this statement might give you an out:

> Oh, to the inevitable 'Why would you want to do it in T-Sql" It's because we're trying to avoid the use of an executable program.

If you are simply trying to avoid a separate executable, how about looping within SQL via a cursor?  Not the best-practice way to go but it would accomplish what you are trying to do.
0
 
ZberteocCommented:
Sorry, I rushed the first answer but i have it in a single select statement.

First create the working table:


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'

Open in new window

0
 
ZberteocCommented:
And here is the magic query:
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

Open in new window

0
 
ZberteocCommented:
No loops no variables. This is how you do it.
0
 
ZberteocCommented:
Not only is simpler but it will be also much faster than any loop approach especially if the table is big. Only need to make sure you have the right indexes on the table, in this case ID and Employee columns.
0
 
Rajkumar GsSoftware EngineerCommented:
Perfect! Zberteoc deserve points if we understood your requirement correctly

Raj
0
 
ZberteocCommented:
Thanks Raj, your chivalry is appreciated, :o)

Regards.
0
 
mmr159Commented:
Ah... coalesce... gets no love until that rare occasion until jumps out and saves the day.

Great work Zberteoc.  The only thing I would tweak would be granularity by diffing seconds.  Then the output (if desired) could be formatted as HH:MM:SS.
0
 
OutOnALimbAlwaysAuthor Commented:
Hey all, Thank you very much for your prompt replies. I'll check everything out  later and see what works. At work now, should be able to take a look tomorrow or tonite.
0
 
OutOnALimbAlwaysAuthor Commented:
Sorry I wasn't able to get back to everyone sooner, and here is a little better explanation.  
Zberteoc is definitely on the right track and has done an amazing job. The only thing lacking is the
'idle' time, which hopefully this will explain:

The employees actually work for sort of a  'think tank' (such as Experts exchange!)  Someone comes in with a Single 'ticket' i.e. problem.
'EmpA' works on it initially, for Min_date - max_Date = 57 mins.
EmpA then consults with his buddy EmpC, who sits on it for
2010-02-03 20:48:15- 2010-02-01 21:25:08 = 2843.
In other words, the problem was immediately passed to empC on Feb 1, but he didn't begin work on it
until Feb 3. Then C passes it back to A on 2010-02-03 20:50:36. A lets it sit Idle for only 3 minutes and passes it on to F. F works on it for  about 6 minutes and passes it back to A!
Crazy, I know, but this actually is the reality of the situation.

The table and SQL below illustrate the situation. This time I took the extraneous data out.
Employee                           TransactionDate

empA            2010-02-01 20:28:40
empA            2010-02-01 21:25:08

empC            2010-02-03 20:48:15
empC            2010-02-03 20:50:36

empA            2010-02-03 20:53:47

empF            2010-02-03 22:08:55
empF            2010-02-03 22:14:13

empA            2010-02-03 22:24:58
empA            2010-02-04 17:45:00

select datediff(mi, '2010-02-01 20:28:40', '2010-02-04 17:45:00') as TotalResolutionTime,

dateDiff(mi, '2010-02-01 20:28:40', '2010-02-01 21:25:08')  --A > A
+ datediff(mi, '2010-02-03 20:50:36', '2010-02-03 20:53:47')  --C to A
+ dateDiff(mi, '2010-02-03 22:14:13', '2010-02-03 22:24:58')  --F to A
+ dateDiff(mi, '2010-02-03 22:24:58', '2010-02-04 17:45:00')  --A to A
as empA,

dateDiff(mi, '2010-02-03 20:48:15', '2010-02-03 20:50:36')   --C to C
+ dateDiff(mi, '2010-02-01 21:25:08', '2010-02-03 20:48:15') --A to C
 as empC,

dateDiff(mi, '2010-02-03 20:53:47', '2010-02-03 22:08:55')  --A to F
 + dateDiff(mi, '2010-02-03 22:08:55', '2010-02-03 22:14:13') --F to F
 as empF

TotalResolutionTime = 4157 = EmpA + EmpC + empF
0
 
LowfatspreadCommented:
like this ?
;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

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you did not post the entire relevant data, as from what you showed, we cannot "deduct" anwhere that a "ticket" passed between employees.

please note that to optimize "best", each step should have a start and stop date, stop date being updated when the ticket is full closed or passed to another person.
0
 
ZberteocCommented:
@justtellmewhatworks:

I don't understant, do you want idle time from A last antry and C first entry to be counted in? If not then we're good. My Solution only takes into consideration the tomes marked for a particular employee. To demonstrate I will post a result from the most inner sub query I use:


id          employee             trandate                anchorID
----------- -------------------- ----------------------- -----------
1           EmpA                 2010-02-01 20:28:40.000 4
2           EmpA                 2010-02-01 20:51:08.000 4
3           EmpA                 2010-02-01 21:25:08.000 4
4           EmpC                 2010-02-03 20:48:15.000 7
5           EmpC                 2010-02-03 20:50:36.000 7
6           EmpC                 2010-02-03 20:50:36.000 7
7           EmpA                 2010-02-03 20:53:47.000 8
8           EmpF                 2010-02-03 22:08:55.000 10
9           EmpF                 2010-02-03 22:14:13.000 10
10          EmpA                 2010-02-03 22:24:58.000 17
11          EmpA                 2010-02-03 22:24:58.000 17
12          EmpA                 2010-02-03 23:44:56.000 17
13          EmpA                 2010-02-03 23:48:02.000 17
14          EmpA                 2010-02-03 23:49:32.000 17
15          EmpA                 2010-02-04 02:06:55.000 17
16          EmpA                 2010-02-04 02:06:55.000 17
17          EmpA                 2010-02-04 17:45:00.000 17


As you can see I group on employeeOD andd anchorID and this makes sure of the dates used.
0
 
ZberteocCommented:
Too many types, I will re-post it.

@justtellmewhatworks:

I don't understant, do you want the idle time from A last entry and C first entry to be counted in? If not then we're good. My Solution only takes into consideration the times marked for a particular employee. To demonstrate I will post the intermediate result from the most inner sub query I use:


id          employee             trandate                anchorID
----------- -------------------- ----------------------- -----------
1           EmpA                 2010-02-01 20:28:40.000 4
2           EmpA                 2010-02-01 20:51:08.000 4
3           EmpA                 2010-02-01 21:25:08.000 4
4           EmpC                 2010-02-03 20:48:15.000 7
5           EmpC                 2010-02-03 20:50:36.000 7
6           EmpC                 2010-02-03 20:50:36.000 7
7           EmpA                 2010-02-03 20:53:47.000 8
8           EmpF                 2010-02-03 22:08:55.000 10
9           EmpF                 2010-02-03 22:14:13.000 10
10          EmpA                 2010-02-03 22:24:58.000 17
11          EmpA                 2010-02-03 22:24:58.000 17
12          EmpA                 2010-02-03 23:44:56.000 17
13          EmpA                 2010-02-03 23:48:02.000 17
14          EmpA                 2010-02-03 23:49:32.000 17
15          EmpA                 2010-02-04 02:06:55.000 17
16          EmpA                 2010-02-04 02:06:55.000 17
17          EmpA                 2010-02-04 17:45:00.000 17


As you can see I group on employee and anchorID and this makes sure of the dates used. The only times that count fro employee A are:

1           EmpA                 2010-02-01 20:28:40.000 4
2           EmpA                 2010-02-01 20:51:08.000 4
3           EmpA                 2010-02-01 21:25:08.000 4

7           EmpA                 2010-02-03 20:53:47.000 8

10          EmpA                 2010-02-03 22:24:58.000 17
11          EmpA                 2010-02-03 22:24:58.000 17
12          EmpA                 2010-02-03 23:44:56.000 17
13          EmpA                 2010-02-03 23:48:02.000 17
14          EmpA                 2010-02-03 23:49:32.000 17
15          EmpA                 2010-02-04 02:06:55.000 17
16          EmpA                 2010-02-04 02:06:55.000 17
17          EmpA                 2010-02-04 17:45:00.000 17

However the entry of line 7 can't produce any time because it stands by itself as there is no interval, a question that had been raised by RajkumarGS to which you haven't answered yet.

I think all this issue is not very clear yet and also the data is insufficient.

0
 
ZberteocCommented:
@Lowfatspread:

CTE is nice and eye catching but is not necessary and doesn't produce any real advantage here. Beside that is not yet adopted by all DBMSs out there.

Point is that my solution will work in any database system. One of the big advantages that CTE brings is the easy implementation of easy recursion, when required.
0
 
LowfatspreadCommented:
@zbertoc  
1) hes asking for a T-Sql solution
2) cte isn't only for recursion... its main advantage is for readability and maintenance and for better documentation
    its akin to a COBOL copy statement or C include

also we need to take angelIII point into consideration, i meant to add a comment myself ...

until the exact nature of the data being recorded can be ascertained, and its impossible to provide a true solution...

the table data doesn't appear optimal for the problem being defined.
0
 
ZberteocCommented:
Agreed, I was just explaining my preference for a non CTE solution in this case.

"the table data doesn't appear optimal for the problem being defined."

The problem we have here is that maybe this is how the system he's using is currently working with this table structure, in which case there is not much to do unless this structure is changed but it might not be possible is he doesn't own it.
0
 
LowfatspreadCommented:
yes

on the second point i think/hope the author has over simplified the situation, which doesn't help us

we need to understand the "ticket id" key for the system, and the exact nature of the recording mechanism/process

i'd also like him to clarify what happens when the process goes over a "midnight" boundary...(do these guys work shifts?) is a days work always closed off with an entry , or is there a status column on the row to indicate the action being undertaken....
0
 
OutOnALimbAlwaysAuthor Commented:
Zberteoc- Yes, sorry didn't make that clear in the first place. The idle time from A last entry to C first entry should be counted in. That First idle time is given by dateDiff(mi, '2010-02-01 21:25:08', '2010-02-03 20:48:15')

Here is a walkthrough:
EmpA receives the ticket on 2010-02-01 20:28:40. He works on it until 2010-02-01 21:25:08. His time so far is 57 minutes.  He then passes it to empC; the time he passes it is the last he worked on it--
2010-02-01 21:25:08. So C LETS IT SIT IDLE until 2010-02-03 20:48:15.  Therefore, we must add
dateDiff(mi, '2010-02-01 21:25:08', '2010-02-03 20:48:15') to C's Time. (Sorry, my datediffs in my last comment above are out of order.)  The rest of C's time is given by dateDiff(mi, '2010-02-03 20:48:15', '2010-02-03 20:50:36')   --C to C

In all Cases, the 'idle time' to be added to the current employee is the last record of the last employee to work on it to the first record of the current employee.

Please see my manual solution above using datediff's. We need to add in the idle times for A to C,
C back to A, A to F, F back to A.  The final answer is  A = 1231, C = 2845, F = 81. The idle times are
A = 13,  c= 2843, F = 75.
You came up with A= 1218, C =2, F = 6.
 If you are able to add these idle times into your query and also get the same answer with the stripped down table I submitted in my last comment you will come up with the correct answer.

Think of it like this: I have a problem. I give it to Person A. Person A works on it for 57 minutes, and then gives it to person C. However, Person C doesn't work on it until 2 days later. However, he is supposed to be working on it, so we have to say that his is the time he does work on it Plus 2 days.
By the time the problem is solved, 4157 minutes has elapsed.
0
 
ZberteocCommented:
In this case all you have to do is to get the difference between last entry and first entry of a ticket, and I assume that the data you gave as example refers to 1 ticket only. Is that the case?
0
 
ZberteocCommented:
If I execute:


select
      Employee,
      datediff(mi, min(trandate),max(trandate)) as MinSpent
from
      emp_time
group by
      Employee
order by
      Employee


I get


Employee             MinSpent
-------------------- -----------
EmpA                 4157
EmpC                 2
EmpF                 6
0
 
OutOnALimbAlwaysAuthor Commented:
LowFat-- As far as off hours and holiday hours, See my last question here on EE. I implemented a function that just counts the legitimate employee hours. Lol, If I can't work the function into the solution to this problem I'll  have to post another question!. I did strip out  on my second comment the 'in between' data, leaving only the max and min dates to try to simplify things. As I say, The query run on the stripped down table should match the query run on the original table, which zberteoc has kindly scripted out.

The only other data I left out, that I possess, is a comment. That relevant comment is that Emp A passed ticket to Emp C. This comment is on the 2010-02-01 21:25:08 record of emp A. However, as I say, this is always the case that the last record date of the previous employee needs to be subtracted from the first record of the current employee and added in to the current employee's time.
0
 
OutOnALimbAlwaysAuthor Commented:
Angell and MMR--There  is only ONE ticket, sorry didn't answer earlier. That one ticket, or think of it as a problem, is bounced around from one employee to another.  OH- I should make it plain also that the transaction Dates are dates when the record was entered. I give you a problem at 3:39 pm on oct 23 and you record the time I gave it to  you. That is your start time. Your end time is when you either pass it on to someone else or Solve it.  

So all the date is there; it just needs to be figured out.
0
 
OutOnALimbAlwaysAuthor Commented:
zberteoc-No. That's the same query I had in the first place, which was wrong. You were on the right track with your first query... There is one ticket, yes, but it is passed from 'employee' to 'employee'
We want to know the total time, work time + idle time,  each employee spent on it. If you could just take your original query and add in the idle time intervals, upon change of employee, between last transdate employee 1 and first trans date employee 2, and add that time interval to employee 2 actual work time, you will have solved the problem.

The only other thing is the query run on the stripped down table should match the query done on the first table you scripted out.

Call me EmpA. Let's call you EmpC. Say I attempt to solve a problem, as I have, and cannot.  I record the time I passed the problem to you, say at 8;00 am monday morning. I record that at 8 am the problem is now yours. The max transaction Date of 8 am is in Emp A's last record. It's his last record because he passes it to EmpC. You, EmpC,  take a 2 hour break. You actually work on it from 10 am monday morning and solve it at 11:00 am monday morning. EmpC's  records 10 am in his first record and 11 am in his second record.

Your first query would figure out your time as 1 hour. That's good, you are on the right track. But since you received the problem at 8 am,  during regular work hours. the right answer would be 3 hours that you worked on it. To me you took three hours to get it done.

But if you didn't solve it and passed it to Emp F  at 11 am then EmpF's start time would be at 11 Am.
0
 
LowfatspreadCommented:
what does this produce?

;with cte as (
  select employee,transactiondate
      ,row_number() over ( 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.rn<b.rn
  where not exists (select employee from cte as x
                      where x.employee=a.employee
                       and x.rn = a.rn-1
                   )
   and not exists (select employee from cte as x
                    where 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.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
0
 
ZberteocCommented:
Here:
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

Open in new window

0
 
OutOnALimbAlwaysAuthor Commented:
Lowfat- It produces
EmpA      1239
EmpC      2902
EmpF      81

With both the stripped down table and the full table  scripted by raj and zberteoc.

Zberteoc produces the correct answer,
EmpA      13      1218      1231
EmpC      2843      2      2845
EmpF      75      6      81       on the full table but an incorrect one

empA                                1279      56      1335
empC                                2846      3      2849
empF                                163      0      163    on the stripped down table.

But you guys are getting real close. BTW, Ty to raj for getting the Ball rolling here with the first semi  correct answer.
0
 
ZberteocCommented:
Al I can do is to give you intermediate results from my sub queries and tell me what is wrong. I changed a bit the query to make it more clear, i added
assign_time column to be the max time from previous employee and then i calculate the difference at the next level:

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

Open in new window

0
 
ZberteocCommented:
Results subquery pass (most inner):

id          employee             trandate                anchorID    assign_time
----------- -------------------- ----------------------- ----------- -----------------------
1           EmpA                 2010-02-01 20:28:40.000 4           2010-02-01 20:28:40.000
2           EmpA                 2010-02-01 20:51:08.000 4           2010-02-01 20:28:40.000
3           EmpA                 2010-02-01 21:25:08.000 4           2010-02-01 20:28:40.000
4           EmpC                 2010-02-03 20:48:15.000 7           2010-02-01 21:25:08.000
5           EmpC                 2010-02-03 20:50:36.000 7           2010-02-01 21:25:08.000
6           EmpC                 2010-02-03 20:50:36.000 7           2010-02-01 21:25:08.000
7           EmpA                 2010-02-03 20:53:47.000 8           2010-02-03 20:50:36.000
8           EmpF                 2010-02-03 22:08:55.000 10          2010-02-03 20:53:47.000
9           EmpF                 2010-02-03 22:14:13.000 10          2010-02-03 20:53:47.000
10          EmpA                 2010-02-03 22:24:58.000 17          2010-02-03 22:14:13.000
11          EmpA                 2010-02-03 22:24:58.000 17          2010-02-03 22:14:13.000
12          EmpA                 2010-02-03 23:44:56.000 17          2010-02-03 22:14:13.000
13          EmpA                 2010-02-03 23:48:02.000 17          2010-02-03 22:14:13.000
14          EmpA                 2010-02-03 23:49:32.000 17          2010-02-03 22:14:13.000
15          EmpA                 2010-02-04 02:06:55.000 17          2010-02-03 22:14:13.000
16          EmpA                 2010-02-04 02:06:55.000 17          2010-02-03 22:14:13.000
17          EmpA                 2010-02-04 17:45:00.000 17          2010-02-03 22:14:13.000




Results sub query total_min:

employee             anchorID    min_spent   min_idle
-------------------- ----------- ----------- -----------
EmpA                 4           57          0
EmpC                 7           2           2843
EmpA                 8           0           3
EmpF                 10          6           75
EmpA                 17          1161        10


Final results:

employee             total_min_idle total_min_work total_min_spent
-------------------- -------------- -------------- ---------------
EmpA                 13             1218           1231
EmpC                 2843           2              2845
EmpF                 75             6              81
0
 
OutOnALimbAlwaysAuthor Commented:
Ok, this is weird. I used Raj's script to create a test table called Emp_TIme_Stripped. As you see, I commented out  the non-relevant data:
drop table emp_Time_stripped
create table Emp_Time_Stripped
(
      ID int,
      Employee char(4),          
      TransactionDate datetime
)

insert into Emp_Time_Stripped
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'

ZBerteocs query now works on both the stripped and the unstripped (Emp_Time) tables.
THE DIFFERENCE IS IN THE ID'S.  THe above query produces 1, 3, 4, 6, 7, 8, 9, 10, 11 for the id's.

So somehow your id's are getting hosed up on data that just has the min and max record. I should say the reality of the situation is that the ID's are like 54701, 54708, 54812... I tried numbering the Id's 1, 3, 5 ,7 9, no good. If zberteoc's query could be written independent of Id's, as Lowfat's does, or if low fat could come up with the right answer, it would be great. I'm kind of favoring zberteoc's though, because it is simpler and doesn't use recursion.
0
 
ZberteocCommented:
The IDs values are irrelevant as long they always grow in the table in the correct chronology and that is how it should happen.

Can you explain how the ID values are messing up my solution?

Thanks.
0
 
OutOnALimbAlwaysAuthor Commented:
zberteoc, I wish I knew how they're messing it up. But if you run the first script and create emp_Time_Stripped it produces the right answer. If you run the script below and create emp_Time_Bad it produces the wrong answer.
Again, everything is the same except the ID's, or my eyes are going bad. In both cases the Id's are sequential.

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'
0
 
ZberteocCommented:
I don't understand what is wrong, this Emp_Time_StrippedBad containes the exact same data except for the middle entries which are irrelevant to th efinal result I executed my query against the Emp_Time_StrippedBad table and I got the exact same result:


employee total_min_idle total_min_work total_min_spent
-------- -------------- -------------- ---------------
EmpA     13             1218           1231
EmpC     2843           2              2845
EmpF     75             6              81

which is normal giving the facts.

Maybe you made a mistake when you adapted the query for Emp_Time_StrippedBad table.

I will post here the exact table(from your post) and code i used:
-- ###############################################
-- 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

Open in new window

0
 
OutOnALimbAlwaysAuthor Commented:
Yes, I did make a mistake! I didn't change end_Time to end_Time_stripped in all places. Careless of me, I should have used search and replace. Anyway, looks like you got it. Thank you, this query is very important!
0
 
ZberteocCommented:
One more thing. In order to be consistent with the column names and their names after adding the idle part here is the code again. Basically I made sure that the columns related to efective work are called _work and the totl time on the ticket is called _spent now:
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

Open in new window

0
 
OutOnALimbAlwaysAuthor Commented:
Thanks, this helped out a lot. We can now get rid of some slow running vb.net code.
0
 
ZberteocCommented:
Glad I could help.

Regards.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 19
  • 11
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now