(Resolvido) Empacado numa query

[sebola]

Bom dia,

Estou quebrando a cabeça na hora de bolar uma consulta no banco para fins de relatórios.

Tenho uma tabela chamada StatusMotivo que armazena alterações de status em um ticket(este é apenas um exemplo dela):

SMID    SMData    SMSolID    SMTarID    SMStatus    SMMotID    SMGrupoSAC    SmUsuID    DatVencimentoAnterior    DatVencimentoNovo    Contabilizado
4254671    2010-07-31 10:20:57.000    1661576    NULL    0    NULL    N    660448    NULL    NULL    1
4254672    2010-07-31 10:20:58.000    1661576    NULL    1    NULL    N    660448    NULL    NULL    1
4255050    2010-07-31 14:32:37.607    1661576    NULL    5    2    N    483793    NULL    NULL    1
4255051    2010-07-31 14:32:37.750    1661576    NULL    5    2    N    483793    NULL    NULL    1
4255269    2010-07-31 23:42:24.000    1661576    NULL    1    NULL    N    483793    NULL    NULL    0
4255287    2010-07-31 23:45:12.000    1661576    NULL    9    NULL    N    483793    NULL    NULL    0

A idéia é calcular o tempo que um ticket ficou em status 5 (status de aguardando). Para tal devemos subtrair o SMDATA imediatamente após o status 5 do SMDATA onde o status entrou em 5. No exemplo seria "2010-07-31 23:42:24.000" - "2010-07-31 14:32:37.607", porém tem uma condição: deve considerar apenas os status 5 aonde o SMUsuID IN ('34','39','44','53041','215550','215552','297086','575417','647421','660447','824288','836393','1183054','1186491','1211384','1236702','1238615','1238616','1243339','1243340', '243341','1386201','1386203','1390108','1390496','1391115','1391701','1395860','1416049','1644377','1644378','1644379','1644380','1765005','1765006','1765008','1766058', '176059','1769384','1769385','1769386','1769388','1772579','1772580','1772581','1772703','1794160','1794161','1795877','1795878','1795879','1795880','1795881','1795882', '179887','1801164','1801165','1801166','1989797') Outro detalhe, essa tabela deve ter um inner join com uma tabela chamada Solicitacao onde o vínculo será StatusMotivo.SMSolID = Solicitacao.SolID Cheguei a filtrar os dados do mês, mas não tenho ideia de como calcular o

SELECT 
    StatusMotivo.SMSolID,
    StatusMotivo.SMData,
    StatusMotivo.SMStatus,
        (SELECT UsuNome FROM Usuario U WHERE U.UsuID = StatusMotivo.SMUsuID) Responsável    
FROM 
    StatusMotivo
    INNER JOIN Solicitacao ON StatusMotivo.SMSolID = Solicitacao.SolID
WHERE 
    StatusMotivo.SMData BETWEEN '2012-04-01 00:00:00' AND '2012-04-30 23:59:59'
    AND StatusMotivo.SMStatus = '5'
    AND StatusMotivo.SMUsuID IN ('34','39','44','53041','215550','215552','297086','575417','647421','660447','824288','836393','1183054','1186491','1211384','1236702','1238615','1238616','1243339','1243340',
'243341','1386201','1386203','1390108','1390496','1391115','1391701','1395860','1416049','1644377','1644378','1644379','1644380','1765005','1765006','1765008','1766058',
'176059','1769384','1769385','1769386','1769388','1772579','1772580','1772581','1772703','1794160','1794161','1795877','1795878','1795879','1795880','1795881','1795882',
'179887','1801164','1801165','1801166','1989797')
ORDER BY SMData ASC

Falta calcular o tempo que o ticket ficou nesse status de aguardando, pode ser em segundos mesmo com o DATEDIFF(SECOND...)

Obs: Em casos onde o ultimo status é o status 5, ou seja, não foi alterado o status para 1 novamente deve-se considerar o getdate() - SMdata que foi colocado em status 5.

Alguém poderia me dar uma luz? :wacko:

Muito Obrigado

[fulvio]

Boa tarde Sebola,

Fiz um exemplo simples logo abaixo. O retorno está em milisegundos, pois tem duas inserções com status 5 com pouca diferença de tempo.

Dê uma olhada pra ver se clareia as ideias... rs.

-- criação de temporária
CREATE TABLE #StatusMotivo (SMID INT, SMData DATETIME, SMTarID INT, SMStatus INT)

-- inserção dos dados
INSERT INTO #StatusMotivo VALUES (4254671,'2010-07-31 10:20:57.000',1661576, 0)
INSERT INTO #StatusMotivo VALUES (4254672,'2010-07-31 10:20:58.000',1661576, 1 )
INSERT INTO #StatusMotivo VALUES (4255050,'2010-07-31 14:32:37.607',1661576, 5 )
INSERT INTO #StatusMotivo VALUES (4255051,'2010-07-31 14:32:37.750',1661576, 5 )
INSERT INTO #StatusMotivo VALUES (4255269,'2010-07-31 23:42:24.000',1661576, 1 )
INSERT INTO #StatusMotivo VALUES (4255287,'2010-07-31 23:45:12.000',1661576, 9)

-- verificação dos 2 registros com status=4
SELECT * FROM #StatusMotivo WHERE SMStatus=5

-- diferença das datas
SELECT datediff (ms, (SELECT top 1 sm.SMData FROM #StatusMotivo sm WHERE s.SMTarID=sm.SMTarID 
AND SMData <> 5 ORDER BY s.SMData DESC), SMData) Diferenca FROM #StatusMotivo s WHERE s.SMStatus=5

[sebola]

Boa tarde Sebola,

Fiz um exemplo simples logo abaixo. O retorno está em milisegundos, pois tem duas inserções com status 5 com pouca diferença de tempo.

Dê uma olhada pra ver se clareia as ideias... rs.

-- criação de temporária
CREATE TABLE #StatusMotivo (SMID INT, SMData DATETIME, SMTarID INT, SMStatus INT)

-- inserção dos dados
INSERT INTO #StatusMotivo VALUES (4254671,'2010-07-31 10:20:57.000',1661576, 0)
INSERT INTO #StatusMotivo VALUES (4254672,'2010-07-31 10:20:58.000',1661576, 1 )
INSERT INTO #StatusMotivo VALUES (4255050,'2010-07-31 14:32:37.607',1661576, 5 )
INSERT INTO #StatusMotivo VALUES (4255051,'2010-07-31 14:32:37.750',1661576, 5 )
INSERT INTO #StatusMotivo VALUES (4255269,'2010-07-31 23:42:24.000',1661576, 1 )
INSERT INTO #StatusMotivo VALUES (4255287,'2010-07-31 23:45:12.000',1661576, 9)

-- verificação dos 2 registros com status=4
SELECT * FROM #StatusMotivo WHERE SMStatus=5

-- diferença das datas
SELECT datediff (ms, (SELECT top 1 sm.SMData FROM #StatusMotivo sm WHERE s.SMTarID=sm.SMTarID 
AND SMData <> 5 ORDER BY s.SMData DESC), SMData) Diferenca FROM #StatusMotivo s WHERE s.SMStatus=5

Na verdade você matou a xarada, com isso eu consigo resolver o problema! Ajustei um pouco para atender todas as situações, existem casos que o ticket cai em status 5, volta para 1 e novamente vai para status 5 e assim sucessivamente, a sua query estava calculando pelo ultimo registro da tabela onde o status era diferente de 5 e na verdade deveria ser imediatamente o proximo status diferente de 5. Ajustei utilizando como parametro o SMID, veja como ficou:

CREATE TABLE #StatusMotivo (SMID INT, SMData DATETIME, SMTarID INT, SMStatus INT)

-- inserção dos dados
INSERT INTO #StatusMotivo VALUES (4254671,'2010-07-31 10:20:57.000',1661576, 0)
INSERT INTO #StatusMotivo VALUES (4254672,'2010-07-31 10:20:58.000',1661576, 1 )
INSERT INTO #StatusMotivo VALUES (4255050,'2010-07-31 14:32:37.607',1661576, 5 )
INSERT INTO #StatusMotivo VALUES (4255051,'2010-07-31 16:21:30.750',1661576, 5 )
INSERT INTO #StatusMotivo VALUES (4255269,'2010-07-31 23:42:24.000',1661576, 1 )
INSERT INTO #StatusMotivo VALUES (4255287,'2010-07-31 23:45:12.000',1661576, 9)
INSERT INTO #StatusMotivo VALUES (4255289,'2010-08-01 00:03:17.210',1661576, 1)
INSERT INTO #StatusMotivo VALUES (4255290,'2010-08-01 00:05:24.210',1661576, 5)
INSERT INTO #StatusMotivo VALUES (4255290,'2010-08-01 00:09:48.120',1661576, 1)

-- verificação dos 2 registros com status=5
SELECT * FROM #StatusMotivo WHERE SMStatus = 5

-- diferença das datas
SELECT dbo.Function_FormataHoraMinutoSegundo(DATEDIFF(SECOND, SMData,(SELECT TOP 1 sm.SMData FROM #StatusMotivo sm WHERE sm.SMTarID=sm.SMTarID AND SMStatus <> 5 AND SMID > (SELECT TOP 1 SMID FROM #StatusMotivo WHERE SMStatus = 5 ORDER BY SMData DESC)))) Diferenca FROM #StatusMotivo S WHERE S.SMStatus = 5

Muito obrigadoo fulvio :D

[fulvio]

rs... isso ai!!! Precisando....