Calculating the prorata of employee calls per Business Unit in SQL Server 2014 -


i establishing business rules determine number of active call center agents per 30 minutes intervals.

we have 2 types of agents: billing & technical ops.

we've let billing agents take technical calls, , vice-versa.

i have written following code returns call count per agent, per interval , per call type:

select      hagent.[row_date],     case         when len([starttime]) = 1 '00:00'        when len([starttime]) = 2 '00:30'        when len([starttime]) = 3 (left([starttime],1) + ':' + right([starttime],2))        when len([starttime]) = 4 (left([starttime],2) + ':' + right([starttime],2))      end thetime,     ([logid]) agents,     count(hagent.acdcalls) call_count,     case         when splitmatrix.[business_unit] '%billing%' 'cdn billing'        when splitmatrix.[business_unit] '%techops%' 'cdn tech ops'      end bunit      [kpi_data].[dbo].[dm_cms_hagent] hagent left join      [gemdb].[dbo].[t_prd_ref_xagent_agentlist] agentlist          on agentlist.asp_raw_cmsid1 = hagent.logid  collate database_default          , hagent.row_date between agentlist.[asp_raw_start_sdept] , agentlist.asp_raw_stop_sdept         , hagent.row_date between agentlist.[asp_raw_start_team] , agentlist.[asp_raw_stop_team] left join      [matrix_data].[dbo].[ref_cms_split_matrix] splitmatrix         on splitmatrix.split = hagent.split     row_date >= '2017-07-01'      , [acdcalls] != 0      , (splitmatrix.[business_unit] '%billing%'           or splitmatrix.[business_unit] '%techops%') group      grouping sets (          (row_date, starttime,([logid]), splitmatrix.[business_unit]),          (row_date, starttime,([logid]))     ) order      row_date desc, thetime desc, [logid] desc

the output looks this:

sql output

with agents being unique logid of agent, bunit type of calls taken (when bunit null, it's total number of calls taken agent interval).

from output, calculate active employee count per interval & calls types.

for example, if agent took 2 billing calls & 1 tech ops call, consider 2/3 of agent working billing agent, , 1/3 of agent tech ops agent.

finally group sum of agents ratio per call types, per interval.

i tried using pivot function, without luck.

my expected result this:

https://i.stack.imgur.com/okiul.jpg

any help?

you can use pivot , unpivot on scenario

sample data

create table t_bu (row_date date,  thetime time,  agents int,  call_count int,  bunit varchar(20) )  insert t_bu values ('8/17/2017','9:30',1210893,1,'cdn billing'), ('8/17/2017','9:30',1210893,1,null), ('8/17/2017','9:30',1210870,2,'cdn tech ops'), ('8/17/2017','9:30',1210870,2,null), ('8/17/2017','9:30',1210858,3,'cdn billing'), ('8/17/2017','9:30',1210858,3,null), ('8/17/2017','9:30',1210857,1,'cdn tech ops'), ('8/17/2017','9:30',1210857,1,null), ('8/17/2017','9:30',1210849,1,'cdn billing'), ('8/17/2017','9:30',1210849,1,null), ('8/17/2017','9:30',1210832,2,'cdn billing'), ('8/17/2017','9:30',1210832,1,'cdn tech ops'), ('8/17/2017','9:30',1210832,3,null), ('10/21/2017','10:30',1210857,3,'cdn tech ops'), ('10/21/2017','10:30',1210857,3,null), ('10/21/2017','12:30',1210857,3,'cdn tech ops'), ('10/21/2017','12:30',1210857,3,null), ('10/21/2017','12:30',1210849,4,'cdn billing'), ('10/21/2017','12:30',1210849,4,null), ('10/21/2017','12:30',1210832,3,'cdn billing'), ('10/21/2017','12:30',1210832,5,'cdn tech ops'), ('10/21/2017','12:30',1210832,8,null);

sql query (dynamic)

declare @cols  nvarchar(max),         @query nvarchar(max)  set @cols = stuff((select distinct ',' + quotename(case when t.bunit null 'total' else t.bunit end)                       t_bu t -- use table name                       xml path(''), type                   ).value('.', 'nvarchar(max)'),1,1,'')  set @query = 'with t_comp               (select row_date, thetime, sum(cast([cdn billing] float)/cast([total] float)) [cdn billing], sum(cast([cdn tech ops] float)/cast([total] float)) [cdn tech ops]                  (select agents, row_date, thetime, ' + @cols +               '           (select agents,                                        row_date,                                       thetime,                                       case when bunit null ''total'' else bunit end bunit,                                       call_count                                  t_bu -- use table name                               ) x                         pivot (max(call_count)                           bunit in (' + @cols + ')) p                        )                 group row_date, thetime                )                select row_date, thetime, call_type, active_agents                  t_comp                unpivot (active_agents                    call_type in ([cdn billing], [cdn tech ops])) u'  execute(@query)  go

result

row_date    thetime             call_type       active_agents 2017-08-17  09:30:00.0000000    cdn billing     3.66666666666667 2017-08-17  09:30:00.0000000    cdn tech ops    2.33333333333333 2017-10-21  10:30:00.0000000    cdn tech ops    1 2017-10-21  12:30:00.0000000    cdn billing     1.375 2017-10-21  12:30:00.0000000    cdn tech ops    1.625

Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more