Brown,
Segue um exemplo:
IF(OBJECT_ID('TabelaHoraBrown') IS NOT NULL)
DROP TABLE TabelaHoraBrown
CREATE TABLE TabelaHoraBrown
(
Id_Screen VARCHAR(100),
MOV_DATE VARCHAR(100),
MOV_TIME VARCHAR(100),
Operation VARCHAR(100)
)
INSERT INTO TabelaHoraBrown(Id_Screen, MOV_DATE, MOV_TIME, Operation)
VALUES
('Tela_01','20.3.2013','9:56.17','LOGIN')
,('Tela_02','21.3.2013','11:56.23','LOGIN')
,('Tela_01','20.3.2013','10:11.40','LOGOUT')
,('Tela_03','21.3.2013','12:56.17','LOGIN')
,('Tela_04','21.3.2013','11:56.23','LOGIN')
,('Tela_02','20.3.2013','16:11.40','LOGOUT')
,('Tela_03','21.3.2013','15:11.40','LOGOUT')
,('Tela_04','21.3.2013','17:11.40','LOGOUT')
,('Tela_01','23.3.2013','9:56.17','LOGIN')
,('Tela_01','25.3.2013','11:56.23','LOGOUT')
GO
--Função de tratativa
IF(OBJECT_ID('IntToMinutes') IS NOT NULL)
DROP FUNCTION IntToMinutes
GO
CREATE FUNCTION [dbo].[IntToMinutes]
(
@m smallint
)
RETURNS nvarchar(5)
AS
BEGIN
DECLARE @c datetime
select @c = dateadd(mi,@m,'00:00')
RETURN convert(nvarchar(5), @c, 108)
END
GO
--SELECT FINAL
SET DATEFORMAT DMY
;WITH CTE(Tela, Data, Hora, Operacao, RNK)
AS
(
SELECT Id_Screen, MOV_DATE, MOV_TIME, Operation, ROW_NUMBER() OVER(PARTITION BY Id_Screen, Operation ORDER BY MOV_DATE + MOV_TIME) As RNK
FROM TabelaHoraBrown
)
SELECT
a.Tela,
a.Data,
[dbo].[IntToMinutes](DATEDIFF(MINUTE, a.Hora, (SELECT b.Hora FROM CTE b WHERE b.RNK = a.RNK AND b.Tela = a.Tela AND b.Operacao LIKE 'LOGOUT'))) As DiferencaLogada
FROM CTE a
WHERE a.Operacao LIKE 'LOGIN'
Fabrizzio A. Caputo
MCT
Certificações:
Oracle OCA 11g
MCITP SQL Server 2008 Implementation and Maintenance
MCITP SQL Server 2008 Developer
ITIL V3 Foundation
Blog Pessoal: www.fabrizziocaputo.wordpress.com
Twitter: @FabrizzioCaputo
Email: fabrizzio.antoniaci@gmail.com
