Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision		 Previous revision
vendors:cisco:uc:icm:sql [2023/07/04 08:53] – [Number of Named / Distinct Agents over the last X Days (in this example 30)] gerardorourkevendors:cisco:uc:icm:sql [2024/12/06 15:03] (current) – [UCCE SQL] gerardorourke
Line 6: Line 6:
   *[[vendors:cisco:uc:icm:sql:inventory|UCCE Inventory SQL]]   *[[vendors:cisco:uc:icm:sql:inventory|UCCE Inventory SQL]]
   *[[vendors:cisco:uc:icm:sql:outbound|UCCE Outbound SQL]]   *[[vendors:cisco:uc:icm:sql:outbound|UCCE Outbound SQL]]
 +  *[[vendors:cisco:uc:icm:sql:parsing|Parsing PV Variable Example]]
   *[[vendors:cisco:uc:icm:sql:script|UCCE Script SQL]]   *[[vendors:cisco:uc:icm:sql:script|UCCE Script SQL]]
   *[[vendors:cisco:uc:icm:sql:scriptdata|UCCE Script Data SQL]]   *[[vendors:cisco:uc:icm:sql:scriptdata|UCCE Script Data SQL]]
Line 376: Line 377:
 ===== Total Calls Handled in a Half Hour===== ===== Total Calls Handled in a Half Hour=====
 <code sql> <code sql>
-SELECT SUM(CallsOfferedToHalf)As Calls ,DateTime  +SELECT SUM(CallsOffered)As Calls ,DateTime  
-FROM Peripheral_Half_Hour+FROM Peripheral_Interval
 WHERE DateTime > GetDate()-1 WHERE DateTime > GetDate()-1
 Group BY DateTime Group BY DateTime
 Order By Calls Desc Order By Calls Desc
 +</code>
  
- +<code> 
-select SUM(CallsHandledHalf) AS TotalHalfCalls,DateTime   +SELECT SUM(CallsHandled) AS TotalHandledCalls,DateTime   
-From t_Call_Type_Half_Hour +FROM Call_Type_Interval 
-Where DateTime > GetDate()-1 +WHERE DateTime > GetDate()-1 
-Group by DateTime +GROUP BY DateTime 
-Order By TotalHalfCalls Desc+ORDER BY TotalHandledCalls DESC
 </code> </code>
  
Line 1199: Line 1201:
 </code> </code>
  
 +==== Active Teams =====
 +Look at the distinct agents who have logged in over the last 24 hours - and sort by teams.
 +In Excel - you can gran Teams and remove duplicates to get a clean list of the exact teams therefore used over 24 hours.
  
 +<code sql>
 +SELECT     
 +DISTINCT AL.SkillTargetID,
 +A.PeripheralID,
 +A.PeripheralNumber,
 +A.PersonID, 
 +P.FirstName, 
 +P.LastName, 
 +P.LoginName,
 +T.EnterpriseName AS AgentTeamName
 +
 +FROM Agent_Logout AL
 +Left JOIN Agent A ON A.SkillTargetID = AL.SkillTargetID
 +LEFT JOIN Agent_Team_Member ATM ON A.SkillTargetID = ATM.SkillTargetID
 +LEFT JOIN Agent_Team T ON ATM.AgentTeamID = T.AgentTeamID
 +LEFT JOIN Person P ON A.PersonID = P.PersonID
 +
 +WHERE AL.DbDateTime > GetDate()-1
 +ORDER BY T.EnterpriseName,FirstName,LastName
 +</code>
 ==== Troubleshooting ==== ==== Troubleshooting ====
 On Logger Only by default! On Logger Only by default!
  • vendors/cisco/uc/icm/sql.1688457200.txt.gz
  • Last modified: 2023/07/04 08:53
  • by gerardorourke