Boa Tarde,
Seguinte, estou com um erro e não consigo resolver, preciso de uma consulta que traga: total de Ordens, o total de sucesso, insucesso, em campo, e em atendimento
Porem, ao fazer a consulta, os resultados são totalmente estranhos, por exemplo, o total de OS consta como 2, e o de sucesso consta como 160, insucesso 20, em campo 6, em atendimento 2
Tem algo errado, no minimo o número de OS deveria ser maior do que todos os outros campos.
Como posso ter 160 OS em sucesso, e total de OS 2 ?
haha seria meio impossivel
Emfim, aí esta a minha procedure
PS:. NÃO SEI PQ, MAS O SQL FICA MANDANDO EU POR AQUELES GROUP BY, NÃO CONSEGUI TIRAR ELE DE LÁ, E TENHO QUE TER TODOS ESTER PARAMETROS NA PESQUISA
DECLARE
@PROVIDER_DEAD_LINE INT --DATA DA OS
DECLARE @ID_SERVICE_GROUP INT
-- GRUPO SERVIÇO
DECLARE @ID_SERVICEPROVIDER INT
-- FILIAL
DECLARE @ID_TECHNICAL INT -- ID DO TÉCNICO
DECLARE @ID_SERVICE INT -- TIPO DE ATENDIMENTO
SELECT @PROVIDER_DEAD_LINE = 1331856000
SELECT @ID_SERVICE_GROUP = 24
SELECT @ID_SERVICEPROVIDER = 135
SELECT @ID_TECHNICAL = 332
SELECT @ID_SERVICE = 2
SELECT DISTINCT
TU.USER_NAME AS 'NAME_TECHNICAL',
(SELECT DISTINCT COUNT(SO.ID_SERVICE_ORDER) FROM TB_SERVICE_ORDER WITH(NOLOCK) WHERE TB_SERVICE_ORDER.ACTIVE = 1 AND TB_SERVICE_ORDER.ID_TECHNICAL = SO.ID_TECHNICAL
AND @ID_SERVICE_GROUP IS NULL OR TB_SERVICE_ORDER.ID_SERVICE_GROUP = @ID_SERVICE_GROUP
AND @ID_SERVICE IS NULL OR TB_SERVICE_ORDER.ID_SERVICE = @ID_SERVICE AND (TB_SERVICE_ORDER.PROVIDER_DEAD_LINE >= @PROVIDER_DEAD_LINE AND TB_SERVICE_ORDER.PROVIDER_DEAD_LINE < @PROVIDER_DEAD_LINE + 86400) AND
(@ID_SERVICEPROVIDER IS NULL OR SO.ID_SERVICEPROVIDER = @ID_SERVICEPROVIDER)) AS 'TT_SERVICE_ORDER',
(SELECT DISTINCT COUNT(ID_SERVICE_ORDER) FROM TB_SERVICE_ORDER WITH(NOLOCK) WHERE TB_SERVICE_ORDER.ACTIVE = 1 AND TB_SERVICE_ORDER.ID_TECHNICAL = SO.ID_TECHNICAL AND TB_SERVICE_ORDER.ID_STATUS = 6
AND @ID_SERVICE_GROUP IS NULL OR TB_SERVICE_ORDER.ID_SERVICE_GROUP = @ID_SERVICE_GROUP
AND @ID_SERVICE IS NULL OR TB_SERVICE_ORDER.ID_SERVICE = @ID_SERVICE AND (TB_SERVICE_ORDER.PROVIDER_DEAD_LINE >= @PROVIDER_DEAD_LINE AND TB_SERVICE_ORDER.PROVIDER_DEAD_LINE < @PROVIDER_DEAD_LINE + 86400) AND
(@ID_SERVICEPROVIDER IS NULL OR SO.ID_SERVICEPROVIDER = @ID_SERVICEPROVIDER)) AS 'TT_SUCESS_OS',
(SELECT DISTINCT COUNT(ID_SERVICE_ORDER) FROM TB_SERVICE_ORDER WITH(NOLOCK) WHERE TB_SERVICE_ORDER.ACTIVE = 1 AND TB_SERVICE_ORDER.ID_TECHNICAL = SO.ID_TECHNICAL AND TB_SERVICE_ORDER.ID_STATUS = 5
AND @ID_SERVICE_GROUP IS NULL OR TB_SERVICE_ORDER.ID_SERVICE_GROUP = @ID_SERVICE_GROUP
AND @ID_SERVICE IS NULL OR TB_SERVICE_ORDER.ID_SERVICE = @ID_SERVICE AND (TB_SERVICE_ORDER.PROVIDER_DEAD_LINE >= @PROVIDER_DEAD_LINE AND TB_SERVICE_ORDER.PROVIDER_DEAD_LINE < @PROVIDER_DEAD_LINE + 86400) AND
(@ID_SERVICEPROVIDER IS NULL OR SO.ID_SERVICEPROVIDER = @ID_SERVICEPROVIDER)) AS 'TT_FAILURE_OS',
(SELECT DISTINCT COUNT(ID_SERVICE_ORDER) FROM TB_SERVICE_ORDER WITH(NOLOCK) WHERE TB_SERVICE_ORDER.ACTIVE = 1 AND TB_SERVICE_ORDER.ID_TECHNICAL = SO.ID_TECHNICAL AND TB_SERVICE_ORDER.ID_STATUS = 2
AND @ID_SERVICE_GROUP IS NULL OR TB_SERVICE_ORDER.ID_SERVICE_GROUP = @ID_SERVICE_GROUP
AND @ID_SERVICE IS NULL OR TB_SERVICE_ORDER.ID_SERVICE = @ID_SERVICE AND (TB_SERVICE_ORDER.PROVIDER_DEAD_LINE >= @PROVIDER_DEAD_LINE AND TB_SERVICE_ORDER.PROVIDER_DEAD_LINE < @PROVIDER_DEAD_LINE + 86400) AND
(@ID_SERVICEPROVIDER IS NULL OR SO.ID_SERVICEPROVIDER = @ID_SERVICEPROVIDER)) AS 'TT_FORWARDED_OS',
(SELECT DISTINCT COUNT(ID_SERVICE_ORDER) FROM TB_SERVICE_ORDER WITH(NOLOCK) WHERE TB_SERVICE_ORDER.ACTIVE = 1 AND TB_SERVICE_ORDER.ID_TECHNICAL = SO.ID_TECHNICAL AND TB_SERVICE_ORDER.ID_STATUS = 3
AND @ID_SERVICE_GROUP IS NULL OR TB_SERVICE_ORDER.ID_SERVICE_GROUP = @ID_SERVICE_GROUP
AND @ID_SERVICE IS NULL OR TB_SERVICE_ORDER.ID_SERVICE = @ID_SERVICE AND (TB_SERVICE_ORDER.PROVIDER_DEAD_LINE >= @PROVIDER_DEAD_LINE AND TB_SERVICE_ORDER.PROVIDER_DEAD_LINE < @PROVIDER_DEAD_LINE + 86400) AND
(@ID_SERVICEPROVIDER IS NULL OR SO.ID_SERVICEPROVIDER = @ID_SERVICEPROVIDER)) AS 'TT_ATTENDANCE_OS'
FROM
TB_SERVICE_ORDER SO WITH(NOLOCK)
INNER JOIN TB_TECHNICAL TT ON SO.ID_TECHNICAL = TT.ID_TECHNICAL
INNER JOIN TB_USER TU ON TT.ID_USER = TU.ID_USER
WHERE
SO.ACTIVE = 1
AND (@ID_TECHNICAL IS NULL OR SO.ID_TECHNICAL = @ID_TECHNICAL)
AND (@ID_SERVICE_GROUP IS NULL OR SO.ID_SERVICE_GROUP = @ID_SERVICE_GROUP)
AND (@ID_SERVICEPROVIDER IS NULL OR SO.ID_SERVICEPROVIDER = @ID_SERVICEPROVIDER)
AND (@ID_SERVICE IS NULL OR SO.ID_SERVICE = @ID_SERVICE)
AND (SO.PROVIDER_DEAD_LINE >= @PROVIDER_DEAD_LINE AND SO.PROVIDER_DEAD_LINE < @PROVIDER_DEAD_LINE + 86400)
GROUP BY
SO.PROVIDER_DEAD_LINE,
SO.ID_SERVICE_GROUP,
SO.ID_SERVICE,
SO.ID_TECHNICAL,
TU.USER_NAME,
SO.ID_SERVICEPROVIDER
