API tools faq
paste
Advertisement
Guest User

QueryLlamadasTiempos

a guest
Mar 2nd, 2015
240
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 9.91 KB | None | 0 0
raw download clone embed print report
  1. -- exec RPTEstadosAlvaro 'iccomercial'
  2. ALTER PROCEDURE [dbo].[RPTEstadosAlvaro](@VirtualCC VARCHAR(50))
  3. AS
  4. SET NoCount ON
  5.  
  6.  
  7. DECLARE     @IdTimeZone VARCHAR(50),
  8.             @StartDateTime Datetime,    @EndDateTime Datetime
  9.            
  10. --declare @VirtualCC varchar(50)
  11. --SELECT @VirtualCC = 'atentoperu'
  12.  
  13. SELECT @IdTimeZone = 'PET'
  14. SELECT @StartDateTime =  dateadd(d, -1 , CONVERT(VARCHAR(10),getdate(),121) + ' 00:00:00:000' )
  15. SELECT @EndDateTime =  dateadd(d, -1 , CONVERT(VARCHAR(10),getdate(),121) + ' 23:59:59:999')
  16.  
  17. --SELECT @StartDateTime = '2014-06-01 00:00:00'
  18. --SELECT @EndDateTime  = '2014-06-30 23:59:59'
  19.  
  20. SELECT  @StartDateTime = dbo.GetInvRealDate(@IdTimeZone, @StartDateTime),
  21.         @EndDateTime = dbo.GetInvRealDate(@IdTimeZone , @EndDateTime)
  22.  
  23.  
  24.     SELECT
  25.         TBL_Resumen.[DATE],
  26.         TBL_Resumen.Campaign                                                AS [CAMPAIGN],
  27.         TBL_Resumen.[Actor]                                                 AS [AGENT],
  28.         CONVERT(VARCHAR,MIN( dbo.GetRealDate(@IdTimeZone ,login )),108)     AS [LOGIN],
  29.         CONVERT(VARCHAR,MAX( dbo.GetRealDate(@IdTimeZone ,logout)),108)     AS [LOGOUT],
  30.         dbo.ConvertHourMin(SUM(ISNULL(LoggedState, 0)))                     AS 'TEMPO LOGADO', 
  31.         dbo.ConvertHourMin(SUM(ISNULL(BreakState, 0))) AS 'BreakTime',
  32.         dbo.ConvertHourMin(SUM(ISNULL(ToiletteState, 0))) AS 'ToiletteTime',
  33.         dbo.ConvertHourMin(SUM(ISNULL(TrainingState, 0))) AS 'TrainingTime',
  34.         dbo.ConvertHourMin(SUM(ISNULL(PersonalState, 0))) AS 'PersonalTime',
  35.         dbo.ConvertHourMin(SUM(ISNULL(CustomsState, 0))) AS 'CustomTime',
  36.         dbo.ConvertHourMin(
  37.             SUM(ISNULL(PauseState, 0)) -
  38.             (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0)))
  39.         ) AS 'IdlePauseTime',
  40.         SUM(ISNULL(TotalCalls,0)) AS 'TOTAL CHAMADAS',
  41.         SUM(ISNULL(TotalCallsAttended,0)) AS 'TOTAL CHAMADAS CONSEGUIDAS',
  42.         SUM(ISNULL(TotalCallsAttended,0)) AS 'TOTAL CHAMADAS CONSEGUIDAS',
  43.         SUM(ISNULL(TotalInBoundAttentionTime+TotalOutBoundAttentionTime+TotalInBoundWrapupTime + TotalOutBoundWrapupTime,0)) AS 'TMO',
  44.         CASE    WHEN SUM(ISNULL(TotalCallsAttended,0)) = 0 THEN 0
  45.                 ELSE
  46.                 SUM(ISNULL(TotalInBoundWrapupTime+TotalOutBoundWrapupTime, 0)) / SUM(ISNULL(TotalCallsAttended,0))
  47.         END AS 'TEMPO MÉDIO TABULANDO',
  48.         CASE WHEN
  49.             SUM(ISNULL(TotalCallsAttended,0)) = 0
  50.             THEN 0 ELSE
  51.             SUM(ISNULL(TotalInBoundWrapupTime+TotalOutBoundWrapupTime, 0)) / SUM(ISNULL(TotalCallsAttended,0))
  52.         END AS 'TEMPO TOTAL TABULANDO',
  53.         (  
  54.         SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  55.         (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0)))) AS 'TEMPO TOTAL DESOCUPADO',
  56.        
  57.         SUM(ISNULL(LoggedState, 0)) - (
  58.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  59.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  60.         ) AS 'TEMPO ATIVO',
  61.        
  62.         CASE WHEN
  63.         SUM(ISNULL(LoggedState, 0)) - (
  64.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  65.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  66.         ) = 0 THEN 0 ELSE
  67.        
  68.         ( SUM(ISNULL(TotalInBoundAttentionTime+TotalOutBoundAttentionTime+TotalInBoundWrapupTime + TotalOutBoundWrapupTime,0)) ) * 100 /
  69.         (   SUM(ISNULL(LoggedState, 0)) - (
  70.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  71.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  72.         )) END AS 'OCUPACAO (%)',
  73.        
  74.         CASE WHEN
  75.         SUM(ISNULL(LoggedState, 0)) - (
  76.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  77.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  78.         ) = 0 THEN 0 ELSE
  79.        
  80.         SUM(ISNULL(TotalInBoundWrapupTime,0) + ISNULL(TotalOutBoundWrapupTime,0)) * 100 /
  81.         (   SUM(ISNULL(LoggedState, 0)) - (
  82.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  83.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  84.         )) END
  85.          AS 'TABULACAO (%)',
  86.         SUM(ISNULL(TotalObjetivos,0)) AS 'Vendas',
  87.         CASE WHEN
  88.         SUM(ISNULL(LoggedState, 0)) - (
  89.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  90.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  91.         ) / 3600 = 0 THEN
  92.         SUM(ISNULL(TotalObjetivos,0)) ELSE
  93.         (SUM(ISNULL(TotalObjetivos,0)) * 3600) /
  94.         SUM(ISNULL(LoggedState, 0)) - (
  95.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  96.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  97.         ) END AS 'TOTAL DE VENDAS POR HORA (%)',
  98.         CASE WHEN
  99.         SUM(ISNULL(LoggedState, 0)) - (
  100.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  101.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  102.         ) / 3600 = 0 THEN
  103.         SUM(ISNULL(TotalCallsAttended,0))
  104.         ELSE
  105.         SUM(ISNULL(TotalCallsAttended,0)) * 3600 /
  106.         SUM(ISNULL(LoggedState, 0)) - (
  107.             (   SUM(ISNULL(BreakState, 0)) + SUM(ISNULL(ToiletteState, 0)) + SUM(ISNULL(TrainingState, 0)) + SUM(ISNULL(PersonalState, 0)) + SUM(ISNULL(CustomsState, 0))) +
  108.             (   SUM(ISNULL(PauseState, 0)) - (SUM(ISNULL(AttePauseState,0)) + SUM(ISNULL(WrapupPauseState,0))))
  109.         )      
  110.         END AS 'TOTAL DE CHAMADAS POR HORA'
  111.  
  112.     FROM(
  113.         SELECT 
  114.             VirtualCC,
  115.             CONVERT(VARCHAR(10), dbo.GetRealDate(@IdTimeZone ,CONVERT(datetime, ACSS.[DATE] + ' ' + ACSS.[HOUR])), 120) AS DATE,       
  116.             Actor,         
  117.             LoggedState,        BreakState,     ToiletteState,
  118.             TrainingState,      PersonalState,
  119.             CustomsState,       PauseState,     UnavailableState,
  120.             AttePauseState,     WrapupPauseState,   ActiveState,
  121.             0 AS TotalInBoundAttentionTime,            
  122.             0 AS TotalOutBoundAttentionTime,
  123.             0 AS TotalInBoundHoldTime,
  124.             0 AS TotalOutboundHoldTime,
  125.             0 AS TotalInBoundWrapupTime,   
  126.             0 AS TotalOutBoundWrapupTime,
  127.             0 AS TotalRingBackTime,
  128.             0 AS TotalCalls,
  129.             0 AS TotalCallsAttended,
  130.             0 AS TotalObjetivos,
  131.             Campaign
  132.           FROM dbo.ActorCampaignStatesSummary ACSS (Nolock)
  133.          WHERE VirtualCC LIKE   CASE    WHEN RTrim(LTrim(@VirtualCC)) = '' THEN '%'
  134.                         ELSE @VirtualCC
  135.                     END
  136.            AND Profile = 'Workflow'
  137.            AND Actor <> ''
  138.                AND CONVERT(datetime, ACSS.DATE + ' ' + ACSS.HOUR ) >= @StartDateTime
  139.                AND CONVERT(datetime, ACSS.DATE + ' ' + ACSS.HOUR ) < @EndDateTime
  140.  
  141.          UNION ALL
  142.  
  143.         SELECT 
  144.             VirtualCC,
  145.             CONVERT(VARCHAR(10), dbo.GetRealDate(@IdTimeZone ,CONVERT(datetime, ICAS.[DATE] + ' ' + ICAS.[HOUR])), 120) AS DATE,       
  146.             --ICAS.Hour,               
  147.             Actor, 
  148.             0,                  0,      0,
  149.             0,                  0,
  150.             0,                  0,      0,     
  151.             0,                  0,      0,
  152.             TotalInBoundAttentionTime, 
  153.             TotalOutBoundAttentionTime,
  154.             TotalInBoundHoldTime,
  155.             TotalOutboundHoldTime,
  156.             TotalInBoundWrapupTime,
  157.             TotalOutBoundWrapupTime,
  158.             TotalRingBackTime,
  159.             0 AS TotalCalls,
  160.             0 AS TotalCallsAttended,
  161.             0 AS TotalObjetivos,
  162.             Campaign
  163.           FROM  dbo.InteractionsCampaignActorSummary ICAS (nolock)
  164.         WHERE TYPE = 'TAPI'
  165.         AND VirtualCC LIKE  CASE    WHEN RTrim(LTrim(@VirtualCC)) = '' THEN '%'
  166.                         ELSE @VirtualCC
  167.                     END
  168.             AND CONVERT(datetime, ICAS.DATE + ' ' + ICAS.HOUR ) >= @StartDateTime
  169.             AND CONVERT(datetime, ICAS.DATE + ' ' + ICAS.HOUR ) < @EndDateTime
  170.  
  171.         UNION ALL
  172.             SELECT
  173.                 --'' as Date,
  174.             VirtualCC, 
  175.             CONVERT(VARCHAR(10), dbo.GetRealDate(@IdTimeZone ,CONVERT(datetime, InD.[DATE] + ' ' + Ind.[HOUR])), 120) AS DATE,         
  176.                 --InD.Hour,
  177.                 Actor, 
  178.                 0,                  0,      0,
  179.                 0,                  0,
  180.                 0,                  0,      0,     
  181.                 0,                  0,      0,
  182.                 0 AS TotalInBoundAttentionTime,                
  183.                 0 AS TotalOutBoundAttentionTime,
  184.                 0 AS TotalInBoundHoldTime,
  185.                 0 AS TotalOutboundHoldTime,
  186.                 0 AS TotalInBoundWrapupTime,   
  187.                 0 AS TotalOutBoundWrapupTime,
  188.                 0 AS TotalRingBackTime,
  189.                 (TotalInBound + TotalOutBound ) AS TotalCalls,
  190.                 (TotalInBoundAttended + TotalOutBoundAttended)  AS TotalCallsAttended,
  191.                 TotalGoalManagementResult AS TotalObjetivos,
  192.                 Campaign
  193.             FROM InteractionsCampaignActorSummary InD (nolock)
  194.             WHERE
  195.         VirtualCC LIKE  CASE    WHEN RTrim(LTrim(@VirtualCC)) = '' THEN '%'
  196.                         ELSE @VirtualCC
  197.                     END AND
  198.         TYPE = 'TAPI'
  199.         AND CONVERT(datetime, InD.DATE + ' ' + InD.HOUR ) >= @StartDateTime
  200.         AND CONVERT(datetime, InD.DATE + ' ' + InD.HOUR ) < @EndDateTime
  201.     )   AS TBL_Resumen
  202.     JOIN
  203.         (
  204.             SELECT
  205.                 --VirtualCC,
  206.                 Actor AS AGENT,
  207.                 MIN(StartDate) AS login,
  208.                 MAX(EndDate) AS logout,
  209.                 Campaign AS 'Campaign'
  210.                 --select * from ActorCampaignStateDetail where Actor='agent2' and StartDate > '2013-05-23' and State='Login'
  211.             FROM ActorCampaignStateDetail (nolock)
  212.             WHERE
  213.                     VirtualCC LIKE  CASE    WHEN RTrim(LTrim(@VirtualCC)) = '' THEN '%'
  214.                         ELSE @VirtualCC
  215.                     END AND
  216.             CONVERT(datetime, StartDate ) >= @StartDateTime
  217.             AND CONVERT(datetime, StartDate ) < @EndDateTime
  218.             AND [State] = 'Login'
  219.             GROUP BY ACTOR, Campaign
  220.         ) AS T
  221.             --ON T.VirtualCC = TBL_Resumen.VirtualCC AND
  222.             ON T.AGENT = TBL_Resumen.ACTOR AND T.Campaign = TBL_Resumen.Campaign           
  223.         GROUP BY   
  224.                 TBL_Resumen.[DATE],
  225.                 TBL_Resumen.Actor,TBL_Resumen.Campaign
  226.       ORDER BY  Actor,CONVERT(VARCHAR(10), dbo.GetRealDate(@IdTimeZone ,CONVERT(datetime, [DATE] )), 120)
  227. SET NOCOUNT OFF
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!