Cristiano,
Ok, obrigado pelo retorno, veja se estes outros exemplos que combinam o usa das funções Lag(), Lead(), DateDiff ou até mesmo o operador Cross Apply, podem te ajudar:
-- Identificando e Calculando a diferença entre datas --
Create Table Vendedores
(Codigo INT ,
CodigoSell INT,
TimeSeller DATE,
SellerOrigim INT,
SellerAtual INT,
SellerName VARCHAR(10))
Go
Insert Into Vendedores
Values (1,10,'2020-02-06',50,1000,'PAUL'),
(5,10,'2020-02-17',1500,2000,'JONH'),
(8,10,'2020-02-18',2000,50,'PIERRE'),
(11,10,'2020-02-19',50,1000,'PAUL'),
(18,10,'2020-02-20',1500,2000,'JONH')
Go
Select * From Vendedores
Go
-- Identificar a diferença respeitando esta regra - Paul e Jonh,Jonh e Pierre,Pierre e Paul, Paul e John , e depois agrupar.
-- SQL Server 2008 R2 ou inferior --
Select (V.SellerName + ' --> ' + IsNull(V2.SellerName,'')) As SellerName,
IsNull(DateDiff(D, V.TimeSeller, V2.TimeSeller),0) As 'Tempo em Dias'
From Vendedores V Outer Apply (Select Top 1 SellerName, TimeSeller
From Vendedores V1
Where V.Codigo < V1.Codigo
Order By Codigo) As V2
Go
-- Versão 2012 ou Superior --
Select Concat(V.SellerName, ' --> ',IsNull(V2.SellerName,'')) As SellerName,
IsNull(DateDiff(D, V.TimeSeller, V2.TimeSeller),0) As 'Tempo em Dias'
From Vendedores V Outer Apply (Select Top 1 SellerName, TimeSeller
From Vendedores V1
Where V.Codigo < V1.Codigo
Order By Codigo) As V2
Go
-- Aplicando o Group By através do uso de CTE --
-- SQL Server 2008 R2 ou inferior --
;With CTEDiferencaEntreFornecedores2008 (Sellername, Tempo)
As
(
Select (V.SellerName + ' --> ' + IsNull(V2.SellerName,'')) As SellerName,
IsNull(DateDiff(D, V.TimeSeller, V2.TimeSeller),0) As 'Tempo'
From Vendedores V Outer Apply (Select Top 1 SellerName, TimeSeller
From Vendedores V1
Where V.Codigo < V1.Codigo
Order By Codigo) As V2
)
Select SellerName, Sum(Tempo) As SomaDoTempo
From CTEDiferencaEntreFornecedores2008
Group By Sellername
Order By Sellername
Go
-- Versão 2012 ou Superior --
;With CTEDiferencaFornecedores2012 (SellerName, Tempo)
As
(Select Concat(V.SellerName, ' --> ',IsNull(V2.SellerName,'')) As SellerName,
IsNull(DateDiff(D, V.TimeSeller, V2.TimeSeller),0) As 'Tempo em Dias'
From Vendedores V Outer Apply (Select Top 1 SellerName, TimeSeller
From Vendedores V1
Where V.Codigo < V1.Codigo
Order By V.Codigo) As V2
)
Select SellerName, Sum(Tempo) As SomaDoTempo
From CTEDiferencaFornecedores2012
Group By Sellername
Order By Sellername
Go
-- Identificando o próximo vendedor utilizando a Função Lead() e calculando a diferença através da Função DateDiff() --
Select V.SellerName, V2.SellerName,
V.TimeSeller,
V2.TimeSeller As 'TimeSellerProximo',
IsNull(DateDiff(dd, V.TimeSeller, Lead(V.TimeSeller) Over(Order By V.Codigo)),0) As 'Diferença em Dias'
From Vendedores V Outer Apply (Select Top 1 Codigo, SellerName, TimeSeller From Vendedores V1
Where V.Codigo < V1.Codigo
Order By V.Codigo) As V2
Go
-- Agrupando --
;With CTEDiferencaFornecedores (SellerNameAtual, SellerNameProximo, TimeSellerAnterior, TimeSellerProximo, DiferencaEmDias)
As
(
Select V.SellerName, V2.SellerName,
V.TimeSeller,
V2.TimeSeller,
IsNull(DateDiff(dd, V.TimeSeller, Lead(V.TimeSeller) Over(Order By V.Codigo)),0)
From Vendedores V Outer Apply (Select Top 1 Codigo, SellerName, TimeSeller From Vendedores V1
Where V.Codigo < V1.Codigo
Order By V.Codigo) As V2
)
Select Concat(SellerNameAtual,' - ', SellerNameProximo) As SellerName,
Sum(DiferencaEmDias) As Somatoria
From CTEDiferencaFornecedores
Group By SellerNameAtual, SellerNameProximo
Order By Somatoria
Go
-- Calculando a diferença entre datas desconsiderando os sábados e domingos:
declare @Data datetime set @Data = '2020/01/01'
--Calcula a quantidade de dias entre a data inicial e a data atual, excluindo sbados e domingos soma 1 no fim pois no conta o proprio dia
WITH AllDates AS
( SELECT TOP (DATEDIFF(DAY, @Data, GETDATE()))
D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @Data)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
SELECT WeekDays = COUNT(*) +1 -
--Clcula a quantidade de feriados entre as datas
(select count(*) from FTAFE(NOLOCK)
where convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112) between @Data and GETDATE()
and FTAFE.CD_CIDADE in(0)
and DATEPART(weekday, convert(datetime, convert(varchar, FTAFE.DT_FERIADO), 112) ) not in(6,7))
FROM AllDates
WHERE DATEPART(WEEKDAY, D) NOT IN(6, 7)
Go
Ressalto mais uma vez que estes são exemplos, peço que verifique, veja qual poderá lhe ajudar.
Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]
