Average Handling Time between times - complex

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Average Handling Time between times - complex

Author

Topic

cvanoosbree
Starting Member

6 Posts

Posted - 2005-06-19 : 22:18:29

hi guys,

this is probably not the best place to ask this but i have no clue as to what the query is or should look like. so i am hoping to give you enough info to hopefully get something that resembles waht i need.

** i added this part to hopefully provide more depth


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USERLOGIN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USERLOGIN]
GO

CREATE TABLE [dbo].[USERLOGIN] (
	[USER_ID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FORM_NAME] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MODIFIED_DATE] [datetime] NULL ,
	[ACTION_TYPE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CUST_NO] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO

**

**


INSERT INTO USERLOGIN
                      (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert Report Pending', 20/06/2005 11:12:40 AM, 'UPDT', 689573)

**

the below is a sample db table
===============
USER_ID LOGIN_TIME LOGOUT_TIME FORM_NAME MODIFIED_DATE ACTION_TYPE CUST_NO
lcherney User Alert Report Pending 20/06/2005 11:12:40 AM UPDT 689573
lcherney 20/06/2005 11:13:00 AM User Alert Report Pending LOSF
lcherney 20/06/2005 11:13:02 AM Unbilled LISF
ltran User Alert Report Pending 20/06/2005 11:14:14 AM UPDT 634157
bforward 20/06/2005 11:39:53 AM bill LISF
jheale 20/06/2005 11:47:18 AM Register LISF
ejohnson 20/06/2005 11:56:46 AM Draft Held Disputed Pending LOSF
ejohnson 20/06/2005 11:56:58 AM Unbilled LISF
dburn 20/06/2005 12:16:22 PM REG LISF
ehristoska Customer Activation Pending 20/06/2005 11:19:33 AM UPDT 1334090
ehristoska Customer Activation Pending 20/06/2005 11:20:03 AM UPDT 1334090
ehristoska Customer Activation Pending 20/06/2005 11:20:45 AM UPDT 1332422

to explain the above (hopefully this adds more clarity) is a list of data based on user actions from a form. the Action_Type field shows what a user did ... eg
LIMF = Login to main form (not relivant)
LOMF = Logout of main form (not relivant)
LISF = Login of sub form
LOSF = Logout of sub form
UPDT = an update of the sub form

on a dialy basis, weekly & monthly basis, i need to find out which subforms had how many updates (this shows volume of transactions) and what the average handling time (AHT) of each form was (this shows how long the particular job / form took to complete)

to get my report, i need to create a query that will
.. look at all values in the Action_Type and find all LISF's, LOSF's & UPDT's based on today (for example) and group by Form_Name
.. find the first LISF of each user in each form and then find the first UPDT to calculate the first Average or difference in time.
.. find all other UPDT's per user based on Form_Name and caculate the 2nd UPDT to the next, to the next to get the remaining AHT (difference in time between transcations)
.. find the last UPDT based on that form and LISF and calculate the difference in time
.. calculate the average of all 3 AHT's from the above by Form_Name
.. group all this by Form_Name

the bellow is the sample report i need to generate from the above (hopefully what the data should show). this is a sample report but i have been unable to deliver a real one (all data below is fictional)

Report Ran - 16/06/06
Date Search - 16/06/06 to 16/06/06
Form Name Agent Count Volume AHT (in sec) Total Time (in sec)
Inventory 7 175 459 87163
Customer Activation Pending Totals 3 10 400 3667
DHD Totals 3 3 471 1414

Form Name = Form_Name
Agent Count = How many unique agents actioned that form
Count = How many transactions from all users for that form
AHT = Average time it took for each of those forms for all users
Total Time = calculation of AHT * Count (shows how much time was spent in total doing taht particular form)

i am pretty sure i have confused everyone, so please ask questions ..

hopefully this can be done
thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-20 : 13:13:32

Words of wisdom from Brett:

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

cvanoosbree
Starting Member

6 Posts

Posted - 2005-06-20 : 19:21:19

hey TG,

added those items you wished for (though limitted data, just dont know how to create a script that inserts multiple records)

other than that, got any clues as to how i can pull off that report?

thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-21 : 09:46:39

cvanoosbree,

thanks for adding the DDL/DML. The problem is it doesn't run. (test your code before posting) :)

use single quotes and make sure the number of values matches the number of columns you're inserting. Here is a sample that will work with your DDL:

also, to retain formatting, post you code within code tags
ie: [c o d e] <your code> [/c o d e] (without the spaces)


--to set the date format for inserting data (only affects this session)
set dateformat mdy --(month/day/year)

INSERT INTO USERLOGIN
(USER_ID, LOGIN_TIME, LOGOUT_TIME, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
select 'lcherney', '06/20/2005 10:15:00.000', '06/20/2005 10:16:00.000', 'User Alert Report Pending', '06/20/2005 11:12:40 AM', 'UPDT', 689573 union all
select 'lcherney', '06/20/2005 10:17:00.000', '06/20/2005 10:18:00.000', 'User Alert Report Pending', '06/20/2005 11:15:40 AM', 'LOSF', 689573

While you work on that, I'll give your report some thought...

EDIT:
Include enough sample data to test the code.
Define which datetime columns are used to calc difference between LISF,UPSF,LOSF records.
Include what the results should be for your sample data.

Be One with the Optimizer
TG

cvanoosbree
Starting Member

6 Posts

Posted - 2005-06-23 : 00:02:45

cvanoosbree
Starting Member

6 Posts

Posted - 2005-06-23 : 00:59:07

hard to explain over email.. but i will try to provide more information

thanks for looking into it

cvanoosbree
Starting Member

6 Posts

Posted - 2005-06-23 : 01:39:52

hi TG,

the below link is data of which you requested. figured a data dump should suffice. from the dump, you will be able to tell tht the LOGIN_TIME, LOGOUT_TIME were removed and put into DATE_MODIFIED to keep things simple (look at the last tab as it has the first two tabs in it). i also modified the first post i made to reflect the new table design (as you can tell)

http://www.i-design.cc/test/report/datadump.xls

on a normal basis, the person would open up the application.. that application sends an "LIMF" to ACTION_TYPE and NOW() to DATE_Modified (along with username). LIMF just states taht the aplication was open. after that, an agent chooses a type of work (sub form). once the agent does so, "LISF" is sent to Action_type and the name of the subform to "Form_Name".

work is then presented to them. if they complete a type of work, an "UPDT" is sent to Action_Type along with the modified date (now()).

agent then logs off the job type (LOSF) logging off the subform.. then logs off the main form (LOMF)

as you can tell, its straight simple based on the above.

now when it comes to the reporting of this, i need to find an "LISF" in Action_Type for a user and find that form name. find all "UPDT" within that first "LISF" of that form and find the closest "LOSF" (this tells me the start, all work and the log off of the work).

eg..
first LIMF - first LOSF of a particular form = total time spent on a particular form (TTS)
count all UPDT's between those times = volume of work (VOL)
then need to calculate TTS - VOL = average handling time (AHT) of those transactions

this needs to be run until all forms are calculated based on a dateto and datefrom search

legend
LIMF = Login Main Form
LISF = Login Sub Form
UPDT = Update
LOSF = Log off Sub Form
LOMF = Log off Main Form

i am hoping this provides more input??

thanks for looking into this again

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-23 : 11:55:15

whew...ok,

Here is some code that should get you started. After looking at a sample of you data dump, (sorted by User,Form_name, Modified_date) it looks like the majority it does NOT start with LISF then have UPDTs then end with LOSF so I'm not sure how you'll adjust your logic to fit the data. But anyway, this should give you some ideas on one way to proceed.

I hope it helps.


set nocount on

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[USERLOGIN]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[USERLOGIN]
GO

CREATE TABLE [dbo].[USERLOGIN] (
	[USER_ID] [char] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[FORM_NAME] [char] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[MODIFIED_DATE] [datetime] NULL ,
	[ACTION_TYPE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[CUST_NO] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO


set dateformat dmy

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert Report Pending', '20/06/2005 11:12:40 AM', 'LISF', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert Report Pending', '20/06/2005 11:13:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert Report Pending', '20/06/2005 11:14:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert Report Pending', '20/06/2005 11:15:40 AM', 'LOSF', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert', '20/06/2005 11:12:40 AM', 'LISF', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert', '20/06/2005 11:13:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert', '20/06/2005 11:14:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('lcherney', 'User Alert', '20/06/2005 11:15:40 AM', 'LOSF', 689573)

----
INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert Report Pending', '20/06/2005 11:12:40 AM', 'LISF', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert Report Pending', '20/06/2005 11:13:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert Report Pending', '20/06/2005 11:14:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert Report Pending', '20/06/2005 11:15:40 AM', 'LOSF', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert', '20/06/2005 11:12:40 AM', 'LISF', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert', '20/06/2005 11:13:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert', '20/06/2005 11:14:40 AM', 'UPDT', 689573)

INSERT INTO USERLOGIN (USER_ID, FORM_NAME, MODIFIED_DATE, ACTION_TYPE, CUST_NO)
VALUES     ('cherney', 'User Alert', '20/06/2005 11:15:40 AM', 'LOSF', 689573)

if object_id('tempdb.dbo.#temp1') > 0 drop table #temp1
if object_id('tempdb.dbo.#temp2') > 0 drop table #temp2

--create empty #temp1 table with identity
select	RowID	= identity(int, 1, 1)
	,USER_ID
	,FORM_NAME
	,MODIFIED_DATE
	,ACTION_TYPE 
	,CUST_NO              
into	#temp1              
from	USERLOGIN
where	1=2

--populate #temp1 with proper sequence
insert #temp1 
	(USER_ID
	,FORM_NAME
	,MODIFIED_DATE
	,ACTION_TYPE 
	,CUST_NO)
select	USER_ID
	,FORM_NAME
	,MODIFIED_DATE
	,ACTION_TYPE 
	,CUST_NO              
from	USERLOGIN
order by user_id, form_name, modified_date 

--select into #temp2 with proper sequence and duration from last action
select	isNull(curr.user_id, prev.user_id)		user_id
	,isNull(curr.form_name, prev.form_name) 	form_name
	,isNull(curr.action_type,prev.action_type)	action_type
	,Upd_time = case 
		when curr.user_id = prev.user_id and curr.form_name = prev.form_name 
		then datediff(second,prev.modified_date, curr.modified_date)
		else 0
		end
	,cast(null as int) as Form_Session
into	#temp2
from	#temp1 curr
left join #temp1 prev
		on curr.rowid = prev.rowid+1

------------------------------------------------------
--set form_session values for total aggregations
declare @user_id	varchar(50)
	,@form_name	varchar(50)
	,@fs		int
set @fs = 0

update #temp2 set
	@fs = @fs + 
		case 
		when user_id = @user_id and form_name = @form_name and action_type <> 'LISF'
		then 0
		else 1
		end
	,@user_id = user_id
	,@form_name = form_name
	,Form_session = @fs
------------------------------------------------------	


print 'report data by subform'

select	form_name
	,count(distinct user_id) agent_count
	,sum(tot_trans) tot_trans
	,avg(avg_time) avg_time
	,sum(tot_time) tot_time
from	(
	select	form_name
		,user_id
		,form_session
		,count(*) as tot_trans
		,sum(upd_time) tot_time
		,avg(upd_time) avg_time
	from	#temp2
	where upd_time > 0
	group by form_name, user_id, form_session
	) a
group by form_name

print ''
print 'form session data'
select	form_name
	,user_id
	,form_session
	,count(*) as tot_trans
	,sum(upd_time) tot_time
	,avg(upd_time) avg_time
from	#temp2
where upd_time > 0
group by form_name, user_id, form_session


print ''
print 'raw data'
select * from #temp2

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources