Calcular SLA na minha tablea
22/11/2013
0
Bom dia, preciso calcular o tempo de SLA no atendimento, não encontrei uma maneira de realizarm e gostaria de entender como é feito, sou iniciante em relação a banco de dados.
Aqui segue meu select
http://pt-br.tinypic.com/view.php?pic=287nccz&s=5#.Uo93OcRwp6w (PARA VER UM PRINT DA QUERY)
Acontece o seguinte, usando o DATEDIFF, consigo compara a entrada a saída, isso serviria para a política 24x7. Mas se o ticket que tem horario comercia e abre de noite, ele só pode valer depois das 8.
Exemplo, se o ticket com politica comercial das 09:00 as 18:00, chegar as 22:00 hoje e sair 02:00 AM de amanhã, o tempo dele tem que ser ZERO,
Mas se ele abrir abrir as 22:00 e sair as 10:00 am, tem que contar 1 hora.
Eu não estou conseguindo realizar essa conta, sei que é questão de lógica, mas se conseguir me ajuda, :))) vou ficar muito grato
Aqui segue meu select
use ksubscribers
go
SELECT
A.Ticket,
E.ref AS Politica,
CAST(A.[Data-Entrada] AS smalldatetime) AS Entrada,
CAST(A.[Data-Saida] AS smalldatetime) AS Saída,
C.codeRef AS Stage, D.poolRef AS Pool,
F.username AS AssignTo,
DATEDIFF (mi,A.[Data-Entrada], A.[Data-Saida]) as 'Tempo de atendimento'
--G.summary as Sumario, A.[Data-Saida] - A.[Data-Entrada] Diferença
FROM
ssbr.[ServiceDesk-PoolLog]
AS A INNER JOIN
kasadmin.SDStage AS C ON A.StageFK = C.id LEFT OUTER JOIN
kasadmin.SDPool AS D ON A.PoolFk = D.id INNER JOIN
kasadmin.SDPolicy AS E ON A.PolicyFK = E.id LEFT OUTER JOIN
dbo.partnerUser AS F ON A.AssignFk = F.id left outer join
kasadmin.vbo_SDIncidents_List as G ON A.Ticket = G.ref
--WHERE
--Convert(Char(10),a.[Data-Entrada],20) >= Convert(Char(10), GetDate(),20)
ORDER BY ticket desc
http://pt-br.tinypic.com/view.php?pic=287nccz&s=5#.Uo93OcRwp6w (PARA VER UM PRINT DA QUERY)
Acontece o seguinte, usando o DATEDIFF, consigo compara a entrada a saída, isso serviria para a política 24x7. Mas se o ticket que tem horario comercia e abre de noite, ele só pode valer depois das 8.
Exemplo, se o ticket com politica comercial das 09:00 as 18:00, chegar as 22:00 hoje e sair 02:00 AM de amanhã, o tempo dele tem que ser ZERO,
Mas se ele abrir abrir as 22:00 e sair as 10:00 am, tem que contar 1 hora.
Eu não estou conseguindo realizar essa conta, sei que é questão de lógica, mas se conseguir me ajuda, :))) vou ficar muito grato
Macaulay
Curtir tópico
+ 0
Responder
Posts
22/11/2013
Isaac Jose
boa tarde amigo vc pode criar uma tabela auxiliar com essas datas em especial.. e antes de cair nessa condição primaria que vc tem fazer uma verificação..
exemplo
horario=
case when entrada => 22 and saída <= 2 then 'zero'
when entrada => 22 and saída <= 10 then '1.
else.. condição
de modo que se for' isso vc já pega o valor..
se não for isso ele deve partir para a sua condição..
acho que deve resolver..
duvidas estou a disposição
exemplo
horario=
case when entrada => 22 and saída <= 2 then 'zero'
when entrada => 22 and saída <= 10 then '1.
else.. condição
de modo que se for' isso vc já pega o valor..
se não for isso ele deve partir para a sua condição..
acho que deve resolver..
duvidas estou a disposição
Responder
06/12/2013
Macaulay
Desculpa a demora, mas como isso não era um prioridade para mim, deixei de lado o problema.
Hoje aperfeiçoei um pouco código mas estou tendo problemas em tratar ainda os casos que tenham tempo de atendimento em horario comercial,
Não consigo acertar a sintaxe do meu IF
No caso estou tratando todos como 24x7
mas eu queria colocar um IF para separar as duas politicas: 24x7 e Comercial 09x18
Se politica = 24x7 faça
DATEDIFF (DAY,T.Dentrada, Dsaida) as 'Dias de atendimento',
DATEDIFF (mi,T.Hentrada, Hsaida) as 'Dias de atendimento'
Else
(Aqui estou tentando adaptar um calculo que encontrei para SLA )
Pode me ajudar?
Hoje aperfeiçoei um pouco código mas estou tendo problemas em tratar ainda os casos que tenham tempo de atendimento em horario comercial,
Não consigo acertar a sintaxe do meu IF
USE ksubscribers
GO
DROP TABLE #tmp_data
SELECT
Ticket,
CAST([Data-Entrada] AS date) as Dentrada,
CAST([Data-Entrada] AS time) as Hentrada,
ISNULL(CAST([Data-saida] AS date),getdate()) as Dsaida,
ISNULL(CAST([Data-Saida] AS time),getdate()) as Hsaida
INTO #TMP_data
FROM ssbr.[ServiceDesk-PoolLog]
--select * from #tmp_data
SELECT
MAX (A.Ticket),
MAX (E.ref) AS Politica,
T.Dentrada,
T.Hentrada,
T.Dsaida,
T.Hsaida,
MAX (C.codeRef)AS Stage,
MAX (D.poolRef) AS Pool,
MAX (F.username) AS AssignTo,
DATEDIFF (DAY,T.Dentrada, Dsaida) as 'Dias de atendimento',
DATEDIFF (mi,T.Hentrada, Hsaida) as 'Dias de atendimento'
FROM
ssbr.[ServiceDesk-PoolLog]
AS
A INNER JOIN
kasadmin.SDStage AS C ON A.StageFK = C.id LEFT OUTER JOIN
kasadmin.SDPool AS D ON A.PoolFk = D.id INNER JOIN
kasadmin.SDPolicy AS E ON A.PolicyFK = E.id LEFT OUTER JOIN
dbo.partnerUser AS F ON A.AssignFk = F.id left outer join
kasadmin.vbo_SDIncidents_List as G ON A.Ticket = G.REF INNER JOIN
#TMP_DATA as T on A.Ticket = T.Ticket
No caso estou tratando todos como 24x7
mas eu queria colocar um IF para separar as duas politicas: 24x7 e Comercial 09x18
Se politica = 24x7 faça
DATEDIFF (DAY,T.Dentrada, Dsaida) as 'Dias de atendimento',
DATEDIFF (mi,T.Hentrada, Hsaida) as 'Dias de atendimento'
Else
(Aqui estou tentando adaptar um calculo que encontrei para SLA )
declare @inicio datetime declare @final datetime declare @i int declare @minutos int declare @fimdesemana integer -- Define a data que iniciou select @inicio = (DATA) -- Define a data q terminou de fato select @final = (DATA) -- Conta quantos sabados e domigos existe entre as duas datas select @fimdesemana = 0, @i = 0 while (DATEDIFF(day, @inicio, @final) > @i) begin select @i = @i + 1 if (DATEPART(WEEKDAY, DATEADD(day, @i, @inicio)) not in (1,7)) begin select @fimdesemana = @fimdesemana + 1 end end -- Soma os minutos uteis no periodo informado... subtraindo os finais de semana select @minutos = ((DATEDIFF(day, @inicio, @final) - 1 - @fimdesemana) * 600) + ((18 - DATEPART(HOUR, @inicio)) * 60) - DATEPART(MINUTE, @inicio) + ((DATEPART(HOUR, @final) - 8) * 60) + DATEPART(MINUTE, @final) -- retora a quantidade de horas e minutos (uteis) do intervalo select CONVERT(int, @minutos / 60) as Horas, CONVERT(int, @minutos % 60) as Minutos
Pode me ajudar?
Responder
06/12/2013
Alex Lekao
Ola Macaulay, boa tarde!!!
Nao analisei muito bem o que esta querendo.
vou colocar aqui uma referencia de uso de if e else com sql da uma olhada se te ajuda.
[url]http://technet.microsoft.com/pt-br/library/ms182587.aspx[/url]
Qualquer coisa vamos vendo o que conseguimos resolver.
A questao do Case que o Issac(acho que foi ele quem estava te ajudando... rssr) colocou da para usar tbm dentro do que vc parece estar querendo.
eu se nao me engano uso algo parecido aqui na empresa.
Uma sugestao no seu script, mais por estetica do resultado do que por eficiencia, rsrsr, se vc usar o Convert no Lugar do Cast, vc consegue deixar a data no padrao brasileiro, para exibir, talvez possa ser mais interessante e fique mais facil para analise pq fica mais familiar. rsrsr
Espero que ajude.
Abraco.
Alex - Lekao
Nao analisei muito bem o que esta querendo.
vou colocar aqui uma referencia de uso de if e else com sql da uma olhada se te ajuda.
[url]http://technet.microsoft.com/pt-br/library/ms182587.aspx[/url]
Qualquer coisa vamos vendo o que conseguimos resolver.
A questao do Case que o Issac(acho que foi ele quem estava te ajudando... rssr) colocou da para usar tbm dentro do que vc parece estar querendo.
eu se nao me engano uso algo parecido aqui na empresa.
Uma sugestao no seu script, mais por estetica do resultado do que por eficiencia, rsrsr, se vc usar o Convert no Lugar do Cast, vc consegue deixar a data no padrao brasileiro, para exibir, talvez possa ser mais interessante e fique mais facil para analise pq fica mais familiar. rsrsr
Espero que ajude.
Abraco.
Alex - Lekao
Responder
10/12/2013
Macaulay
Valeu pela ajuda,
;ainda não consegui realizar o if, pois sempre tenho erro de sintaxe.
if [Politica] = '24x7'
set DATEDIFF (DAY,T.Dentrada, Dsaida) as 'Dias de atendimento'
Mas sempre acusa erro no '='
Como eu faço esse if? Juro que ainda não consegui entender esse maldito rsrs
;ainda não consegui realizar o if, pois sempre tenho erro de sintaxe.
if [Politica] = '24x7'
set DATEDIFF (DAY,T.Dentrada, Dsaida) as 'Dias de atendimento'
Mas sempre acusa erro no '='
Como eu faço esse if? Juro que ainda não consegui entender esse maldito rsrs
Responder
10/12/2013
Alex Lekao
Oi... boa tarde!!!
Experimentou fazer usando variavis como o Fabiano indicou?
outra coisa, esse 24x7 eh campo texto normal?
de repente vc definindo uma variavel para isso e alimentando ela com o conteudo do campo nao da certo?
Testa ai e posta aqui se resolveu.
Abraco.
Alex - Lekao
Experimentou fazer usando variavis como o Fabiano indicou?
outra coisa, esse 24x7 eh campo texto normal?
de repente vc definindo uma variavel para isso e alimentando ela com o conteudo do campo nao da certo?
Testa ai e posta aqui se resolveu.
Abraco.
Alex - Lekao
Responder
11/12/2013
Macaulay
Vou procurar um tempo vago para tentar fazer o que vocês me indicaram e postarei o resultado, vlw galera
Responder
11/12/2013
Isaac Jose
Bom dia.
essa opção e mais complicada mais acho q pode te ajudar :)
segue codigo da FN e tabela de feriados para contar apenas dias uteis do periodo. se quizer pode tirar a tabela de feriados.
com isso vc elimina o fds.
create function Fn_Dias_Uteis (@datestart smalldatetime ,@dateend datetime)
returns int
as
begin
declare @count int
select @count = 0
while @datestart <= @dateEnd
begin
if datePart(weekday,@datestart)not in (7,1) and @datestart not in
(select data_feriado from tb_feriado)
select @count = @count +1
select @datestart = dateadd(day,1,@datestart)
end
return @count
end
create table tb_feriado(
data_feriado datetime)
insert into tb_feriado (data_feriado) values ('2014-01-01')
insert into tb_feriado (data_feriado) values ('2014-03-04')
insert into tb_feriado (data_feriado) values ('2014-04-18')
insert into tb_feriado (data_feriado) values ('2014-04-21')
insert into tb_feriado (data_feriado) values ('2014-05-01')
insert into tb_feriado (data_feriado) values ('2014-06-19')
insert into tb_feriado (data_feriado) values ('2014-12-25')
na tabela que vc recebe a data vc ira ignorar os horarios
como no case que deixei no outro post se for das 22 as 10 vc tem 1
22 as 11 vc tem 2
22 as 12 vc tem 3
e assim por diante..
eu por questao de performace costumo separar o campo data... vc pode fazer se quizer com a data e com os minutos.. ai e com vc..
com isso eu faço update em outras tabelas. no seu caso vc deve ter um campo que devera gravar o tempo da SLA correto?
pois bem segue.. os exemplos de update na tb apartir da FN, pegando os parametros de data inicio e fim assim no final vc tera o resultado do tempo gasto .
e so adaptar ao seu ambiente..
update Tb_1
set tintDiasUteis = (
select manutencao.fn_Dias_Uteis((select DataInicio = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
'01'
from Tb_1 ),(SELECT
DataFim = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
CAST(MAX(tintdia)AS VARCHAR(2))
from Tb_1
)))
go
update Tb_2
set tintDiasUteis = (
select manutencao.fn_Dias_Uteis((select DataInicio = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
'01'
from Tb_2 ),(SELECT
DataFim = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
CAST(MAX(tintdia)AS VARCHAR(2))
from Tb_2
)))
Espero que ajude..
abraços att Isaac
essa opção e mais complicada mais acho q pode te ajudar :)
segue codigo da FN e tabela de feriados para contar apenas dias uteis do periodo. se quizer pode tirar a tabela de feriados.
com isso vc elimina o fds.
create function Fn_Dias_Uteis (@datestart smalldatetime ,@dateend datetime)
returns int
as
begin
declare @count int
select @count = 0
while @datestart <= @dateEnd
begin
if datePart(weekday,@datestart)not in (7,1) and @datestart not in
(select data_feriado from tb_feriado)
select @count = @count +1
select @datestart = dateadd(day,1,@datestart)
end
return @count
end
create table tb_feriado(
data_feriado datetime)
insert into tb_feriado (data_feriado) values ('2014-01-01')
insert into tb_feriado (data_feriado) values ('2014-03-04')
insert into tb_feriado (data_feriado) values ('2014-04-18')
insert into tb_feriado (data_feriado) values ('2014-04-21')
insert into tb_feriado (data_feriado) values ('2014-05-01')
insert into tb_feriado (data_feriado) values ('2014-06-19')
insert into tb_feriado (data_feriado) values ('2014-12-25')
na tabela que vc recebe a data vc ira ignorar os horarios
como no case que deixei no outro post se for das 22 as 10 vc tem 1
22 as 11 vc tem 2
22 as 12 vc tem 3
e assim por diante..
eu por questao de performace costumo separar o campo data... vc pode fazer se quizer com a data e com os minutos.. ai e com vc..
com isso eu faço update em outras tabelas. no seu caso vc deve ter um campo que devera gravar o tempo da SLA correto?
pois bem segue.. os exemplos de update na tb apartir da FN, pegando os parametros de data inicio e fim assim no final vc tera o resultado do tempo gasto .
e so adaptar ao seu ambiente..
update Tb_1
set tintDiasUteis = (
select manutencao.fn_Dias_Uteis((select DataInicio = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
'01'
from Tb_1 ),(SELECT
DataFim = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
CAST(MAX(tintdia)AS VARCHAR(2))
from Tb_1
)))
go
update Tb_2
set tintDiasUteis = (
select manutencao.fn_Dias_Uteis((select DataInicio = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
'01'
from Tb_2 ),(SELECT
DataFim = CAST(MAX(BintAno)AS VARCHAR(4))+'-'+
CAST(MAX(tintmes)AS VARCHAR(2))+'-'+
CAST(MAX(tintdia)AS VARCHAR(2))
from Tb_2
)))
Espero que ajude..
abraços att Isaac
Responder
Clique aqui para fazer login e interagir na Comunidade :)
