API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 23rd, 2019
70
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 12.29 KB | None | 0 0
raw download clone embed print report
  1. --Check stats in mart.fact_queue
  2. DECLARE @tzID as int = 2;
  3. DECLARE @startdate as date = '2019-03-01';
  4. DECLARE @enddate as date = '2019-03-20';
  5. DECLARE @startdateID as int;
  6. DECLARE @enddateID as int;
  7. DECLARE @minint as int = 0;
  8. DECLARE @maxint as int = 95;
  9. DECLARE @logobject as int = 2
  10. DECLARE @datasource as int 
  11. SELECT @datasource =  datasource_id FROM mart.sys_datasource WHERE log_object_id = @logobject
  12.  
  13. SELECT @startdateID = date_id from mart.dim_date where date_date = @startdate
  14. SELECT @enddateID = date_id from mart.dim_date where date_date = @enddate;
  15.  
  16. WITH time_zone AS (
  17. SELECT dd.date_date date, tz.local_date_id local_date_id, tz.local_interval_id local_interval,
  18. tz.date_id date_id, tz.interval_id interval
  19. FROM mart.dim_date d
  20. INNER JOIN mart.bridge_time_zone tz ON d.date_id = tz.date_id
  21. INNER JOIN mart.dim_date dd ON dd.date_id = tz.local_date_id
  22. WHERE d.date_id BETWEEN @startdateID AND @enddateID and tz.time_zone_id = @tzID)
  23.  
  24. SELECT convert(date,time_zone.date) date
  25. ,mq.[queue_name]
  26. ,SUM(q.[offered_calls]) offrd
  27. ,SUM(q.[answered_calls]) answ
  28. ,SUM(q.[answered_calls_within_SL]) answ_SLA
  29. ,SUM(q.[abandoned_calls]) aband
  30. ,cast(cast(SUM(q.[talk_time_s])/3600 AS INT) as varchar(5)) + ':' +
  31. cast(cast((SUM(q.[talk_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  32. cast(cast(SUM(q.[talk_time_s]) % 60 AS INT) as varchar(5)) talk_time
  33. ,cast(cast(SUM(q.[after_call_work_s])/3600 AS INT) as varchar(5)) + ':' +
  34. cast(cast((SUM(q.[after_call_work_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  35. cast(cast(SUM(q.[after_call_work_s]) % 60 AS INT) as varchar(5)) ACW
  36. ,cast(cast(SUM(q.[handle_time_s])/3600 AS INT) as varchar(5)) + ':' +
  37. cast(cast((SUM(q.[handle_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  38. cast(cast(SUM(q.[handle_time_s]) % 60 AS INT) as varchar(5)) Handle
  39. ,cast(cast(SUM(q.[time_to_abandon_s])/3600 AS INT) as varchar(5)) + ':' +
  40. cast(cast((SUM(q.[time_to_abandon_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  41. cast(cast(SUM(q.[time_to_abandon_s]) % 60 AS INT) as varchar(5)) Queue_time_aband
  42. ,MAX(q.[longest_delay_in_queue_answered_s]) max_answ_queue
  43. ,MAX(q.[longest_delay_in_queue_abandoned_s]) max_aband_queue
  44. FROM [mart].[fact_queue] q
  45. INNER JOIN time_zone ON q.date_id = time_zone.date_id and time_zone.interval = q.interval_id
  46. INNER JOIN mart.dim_queue mq ON mq.queue_id = q.queue_id
  47. WHERE
  48. --q.queue_id IN (SELECT queue_id FROM mart.dim_queue WHERE datasource_id =  @datasource) AND
  49. --q.queue_id IN (12) AND
  50. (time_zone.local_date_id >= @startdateID AND time_zone.local_date_id <= @enddateID)
  51. and time_zone.local_interval BETWEEN @minint AND @maxint
  52. GROUP BY time_zone.date, mq.[queue_name]
  53. ORDER BY mq.[queue_name], time_zone.date
  54.  
  55. --select * from mart.dim_time_zone
  56.  
  57. --Check stats in dbo.queue_logg
  58. DECLARE @startdate as date = '2019-06-18';
  59. DECLARE @enddate as date = '2019-06-18';
  60. DECLARE @minint as int = 0;
  61. DECLARE @maxint as int = 95;
  62. DECLARE @log_ob as int = 2;
  63.  
  64. select q.date_from,l.orig_desc,q.interval
  65. ,sum([offd_direct_call_cnt]) offrd
  66. ,sum([answ_call_cnt]) answ
  67. ,sum([ans_servicelevel_cnt]) answ_SLA
  68. ,sum([aband_call_cnt]) aband
  69. ,sum([aband_within_sl_cnt]) aband_in_sl
  70. ,sum([aband_short_call_cnt]) aband_short
  71. ,sum([overflow_in_call_cnt]) overflow_in
  72. ,sum([overflow_out_call_cnt]) overflow_out
  73. ,cast(cast(SUM(q.[talking_call_dur])/3600 AS INT) as varchar(5)) + ':' +
  74. cast(cast((SUM(q.[talking_call_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  75. cast(cast(SUM(q.[talking_call_dur]) % 60 AS INT) as varchar(5)) talk_time
  76. ,cast(cast(SUM(q.[wrap_up_dur])/3600 AS INT) as varchar(5)) + ':' +
  77. cast(cast((SUM(q.[wrap_up_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  78. cast(cast(SUM(q.[wrap_up_dur]) % 60 AS INT) as varchar(5)) ACW
  79. ,cast(cast(SUM(q.[queued_and_answ_call_dur])/3600 AS INT) as varchar(5)) + ':' +
  80. cast(cast((SUM(q.[queued_and_answ_call_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  81. cast(cast(SUM(q.[queued_and_answ_call_dur]) % 60 AS INT) as varchar(5)) Queue_time_answ
  82. -- ,cast(cast((SUM(q.[queued_and_answ_call_dur])/SUM([answ_call_cnt]))/3600 AS INT) as varchar(5)) + ':' +
  83. -- cast(cast(((SUM(q.[queued_and_answ_call_dur])/SUM([answ_call_cnt])) % 3600)/60 AS INT) as varchar(5)) + ':' +
  84. -- cast(cast((SUM(q.[queued_and_answ_call_dur])/SUM([answ_call_cnt])) % 60 AS INT) as varchar(5)) Avg_time_answ
  85. ,cast(cast(SUM(q.[queued_and_aband_call_dur])/3600 AS INT) as varchar(5)) + ':' +
  86. cast(cast((SUM(q.[queued_and_aband_call_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  87. cast(cast(SUM(q.[queued_and_aband_call_dur]) % 60 AS INT) as varchar(5)) Queue_time_aband
  88. ,cast(cast(MAX(q.[queued_answ_longest_que_dur])/3600 AS INT) as varchar(5)) + ':' +
  89. cast(cast((MAX(q.[queued_answ_longest_que_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  90. cast(cast(MAX(q.[queued_answ_longest_que_dur]) % 60 AS INT) as varchar(5)) max_answ_queue
  91. ,cast(cast(MAX(q.[queued_aband_longest_que_dur])/3600 AS INT) as varchar(5)) + ':' +
  92. cast(cast((MAX(q.[queued_aband_longest_que_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  93. cast(cast(MAX(q.[queued_aband_longest_que_dur]) % 60 AS INT) as varchar(5)) max_aband_queue
  94. from dbo.queue_logg q
  95. inner join dbo.queues l on l.queue = q.queue
  96. where (date_from >= @startdate AND date_from <= @enddate)
  97. and q.[interval] BETWEEN @minint and @maxint
  98. --and q.[queue] in (153)
  99. and l.log_object_id = @log_ob
  100. group by q.date_from,l.orig_desc,q.interval
  101. order by q.date_from, q.interval
  102.  
  103. --Check stats in dbo.agent_logg
  104. DECLARE @startdate as date = '2019-03-23';
  105. DECLARE @enddate as date = '2019-03-26';
  106. DECLARE @minint as int = 0;
  107. DECLARE @maxint as int = 95;
  108. DECLARE @agent as int = 5;
  109. DECLARE @logobject as int = 2
  110.  
  111. select a.Agent_name
  112. ,q.date_from
  113. --,l.orig_desc
  114. ,sum([answ_call_cnt]) answ
  115. ,cast(cast(SUM(q.[talking_call_dur])/3600 AS INT) as varchar(5)) + ':' +
  116. cast(cast((SUM(q.[talking_call_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  117. cast(cast(SUM(q.[talking_call_dur]) % 60 AS INT) as varchar(5)) talk_time
  118. ,cast(cast(SUM(q.[wrap_up_dur])/3600 AS INT) as varchar(5)) + ':' +
  119. cast(cast((SUM(q.[wrap_up_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  120. cast(cast(SUM(q.[wrap_up_dur]) % 60 AS INT) as varchar(5)) ACW
  121. ,cast(cast(SUM(q.[tot_work_dur])/3600 AS INT) as varchar(5)) + ':' +
  122. cast(cast((SUM(q.[tot_work_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  123. cast(cast(SUM(q.[tot_work_dur]) % 60 AS INT) as varchar(5)) logged_in
  124. ,cast(cast(SUM(q.[avail_dur])/3600 AS INT) as varchar(5)) + ':' +
  125. cast(cast((SUM(q.[avail_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  126. cast(cast(SUM(q.[avail_dur]) % 60 AS INT) as varchar(5)) ready
  127. ,cast(cast(SUM(q.[pause_dur])/3600 AS INT) as varchar(5)) + ':' +
  128. cast(cast((SUM(q.[pause_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  129. cast(cast(SUM(q.[pause_dur]) % 60 AS INT) as varchar(5)) [pause]
  130. ,cast(cast(MAX(q.[pause_dur])/3600 AS INT) as varchar(5)) + ':' +
  131. cast(cast((MAX(q.[admin_dur]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  132. cast(cast(MAX(q.[admin_dur]) % 60 AS INT) as varchar(5)) [admin]
  133. ,sum([transfer_out_call_cnt]) transferred
  134. from dbo.agent_logg q
  135. inner join dbo.queues l on l.queue = q.queue
  136. inner join dbo.agent_info a on q.agent_id = a.Agent_id
  137. where ((date_from >= @startdate) and (date_from <= @enddate))
  138. --agent_id IN (SELECT agent_id FROM dbo.agent_info WHERE log_object_id = @logobject)
  139. --and q.agent_id = @agent
  140. group by a.Agent_name
  141. ,q.date_from
  142. --,l.orig_desc
  143. order by q.date_from
  144. --, l.orig_desc
  145.  
  146. --Check stats in mart.fact_agent
  147. DECLARE @tzID as int = 3;
  148. DECLARE @startdate as date = '2019-03-23';
  149. DECLARE @enddate as date = '2019-03-26';
  150. DECLARE @startdateID as int;
  151. DECLARE @enddateID as int;
  152. DECLARE @minint as int = 0;
  153. DECLARE @maxint as int = 95;
  154. DECLARE @agent as int = 22;
  155. DECLARE @logobject as int = 2
  156. DECLARE @datasource as int 
  157. SELECT @datasource =  datasource_id FROM mart.sys_datasource WHERE log_object_id = @logobject
  158.  
  159. SELECT @startdateID = date_id from mart.dim_date where date_date = @startdate
  160. SELECT @enddateID = date_id from mart.dim_date where date_date = @enddate;
  161.  
  162. WITH time_zone AS (
  163. SELECT dd.date_date date, tz.local_date_id local_date_id, tz.local_interval_id local_interval,
  164. tz.date_id date_id, tz.interval_id interval
  165. FROM mart.dim_date d
  166. INNER JOIN mart.bridge_time_zone tz ON d.date_id = tz.date_id
  167. INNER JOIN mart.dim_date dd ON dd.date_id = tz.local_date_id
  168. WHERE d.date_id BETWEEN @startdateID AND @enddateID and tz.time_zone_id = @tzID)
  169.  
  170. SELECT convert(date,time_zone.date) date
  171. ,l.acd_login_name
  172. ,SUM(q.[direct_outbound_calls]) direct_out
  173. ,SUM(q.[direct_incoming_calls]) direct_in
  174. ,cast(cast(SUM(q.[logged_in_time_s])/3600 AS INT) as varchar(5)) + ':' +
  175. cast(cast((SUM(q.[logged_in_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  176. cast(cast(SUM(q.[logged_in_time_s]) % 60 AS INT) as varchar(5)) logged_in
  177. ,cast(cast(SUM(q.[ready_time_s])/3600 AS INT) as varchar(5)) + ':' +
  178. cast(cast((SUM(q.[ready_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  179. cast(cast(SUM(q.[ready_time_s]) % 60 AS INT) as varchar(5)) ready
  180. ,cast(cast(SUM(q.[not_ready_time_s])/3600 AS INT) as varchar(5)) + ':' +
  181. cast(cast((SUM(q.[not_ready_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  182. cast(cast(SUM(q.[idle_time_s]) % 60 AS INT) as varchar(5)) [pause]
  183. ,cast(cast(SUM(q.[not_ready_time_s])/3600 AS INT) as varchar(5)) + ':' +
  184. cast(cast((SUM(q.[idle_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  185. cast(cast(SUM(q.[idle_time_s]) % 60 AS INT) as varchar(5)) idle
  186. FROM [mart].[fact_agent] q
  187. INNER JOIN time_zone ON q.date_id = time_zone.date_id and time_zone.interval = q.interval_id
  188. INNER JOIN [mart].[dim_acd_login] l on q.acd_login_id = l.acd_login_id
  189. WHERE
  190. --q.acd_login_id = @agent AND
  191. --q.acd_login_id IN (SELECT acd_login_id FROM mart.dim_acd_login WHERE datasource_id =  @datasource)
  192. (time_zone.local_date_id >= @startdateID AND time_zone.local_date_id <= @enddateID)
  193. AND time_zone.local_interval BETWEEN @minint AND @maxint
  194. GROUP BY time_zone.date, l.acd_login_name
  195. ORDER BY l.acd_login_name, time_zone.date
  196.  
  197. --Check stats in mart.fact_agent_queue
  198. DECLARE @tzID as int = 3;
  199. DECLARE @startdate as date = '2019-06-18';
  200. DECLARE @enddate as date = '2019-06-18';
  201. DECLARE @startdateID as int;
  202. DECLARE @enddateID as int;
  203. DECLARE @minint as int = 0;
  204. DECLARE @maxint as int = 95;
  205. DECLARE @agent as int = 935;
  206. DECLARE @logobject as int = 2
  207. DECLARE @datasource as int 
  208. SELECT @datasource =  datasource_id FROM mart.sys_datasource WHERE log_object_id = @logobject
  209.  
  210. SELECT @startdateID = date_id from mart.dim_date where date_date = @startdate
  211. SELECT @enddateID = date_id from mart.dim_date where date_date = @enddate;
  212.  
  213. WITH time_zone AS (
  214. SELECT dd.date_date date, tz.local_date_id local_date_id, tz.local_interval_id local_interval,
  215. tz.date_id date_id, tz.interval_id interval
  216. FROM mart.dim_date d
  217. INNER JOIN mart.bridge_time_zone tz ON d.date_id = tz.date_id
  218. INNER JOIN mart.dim_date dd ON dd.date_id = tz.local_date_id
  219. WHERE d.date_id BETWEEN @startdateID AND @enddateID and tz.time_zone_id = @tzID)
  220.  
  221. SELECT convert(date,time_zone.date) date
  222. ,mq.[queue_name]
  223. ,SUM(q.[answered_calls]) answ
  224. ,SUM([transfered_calls]) transfer
  225. ,cast(cast(SUM(q.[talk_time_s])/3600 AS INT) as varchar(5)) + ':' +
  226. cast(cast((SUM(q.[talk_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  227. cast(cast(SUM(q.[talk_time_s]) % 60 AS INT) as varchar(5)) talk_time
  228. ,cast(cast(SUM(q.[after_call_work_time_s])/3600 AS INT) as varchar(5)) + ':' +
  229. cast(cast((SUM(q.[after_call_work_time_s]) % 3600)/60 AS INT) as varchar(5)) + ':' +
  230. cast(cast(SUM(q.[after_call_work_time_s]) % 60 AS INT) as varchar(5)) ACW
  231. FROM [mart].[fact_agent_queue] q
  232. INNER JOIN time_zone ON q.date_id = time_zone.date_id and time_zone.interval = q.interval_id
  233. INNER JOIN mart.dim_queue mq ON mq.queue_id = q.queue_id
  234. WHERE
  235. --q.queue_id IN (12) AND
  236. --q.queue_id IN (SELECT queue_id FROM mart.dim_queue WHERE datasource_id = @datasource) AND
  237. (time_zone.local_date_id >= @startdateID AND time_zone.local_date_id <= @enddateID)
  238. and time_zone.local_interval BETWEEN @minint AND @maxint
  239. and q.acd_login_id = @agent
  240. GROUP BY time_zone.date, mq.[queue_name]
  241. ORDER BY mq.[queue_name], time_zone.date
  242.  
  243. --select * from mart.dim_time_zone
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!