Difference between dates on multiple lines

Question

Is there any way to make a DATEDIFF/LEAD/LAG in SQL Server to calculate the difference between dates taking into account the date of the previous/next record.

SELECT

SELECT NUM, CLIENTE, EMISSAO
  FROM PEDIDOS
 WHERE CLIENTE = '06.028'
 ORDER BY EMISSAO DESC

Upshot

NUM     CLIENTE EMISSAO
35890H  06.028  20160226
y35888  06.028  20160225
y33449  06.028  20160122
y33046  06.028  20160111
y28763  06.028  20151125
AS9816  06.028  20151118

Expected Result:

ROW CODIGO EMISSAO  TEMPO
1   06.028 20160226 5     -=> Utiliza o GETDATE() ou Deixa Zerado (0)
2   06.028 20141030 484   -=> Utiliza a data da linha 1
3   06.028 20141030 0     -=> Utiliza a data da linha 2
4   06.028 20140930 30    -=> Utiliza a data da linha 3
5   06.028 20140612 145   -=> Utiliza a data da linha 4
6   05.127 20160102 29    -=> Utiliza o GETDATE() ou Deixa Zerado (0)
7   05.127 20151225  9    -=> Utiliza a data da linha 6
8   05.127 20151205 20    -=> Utiliza a data da linha 7

Obs: the column ROW is just to display the line I’m referencing in the description (-=>) beside.

When changing the customer, stop using the previous customer date and start all over again.

Answer 1

Try to do it this way

declare @Tabela1 table
(
   ROW int IDENTITY(1,1) NOT NULL, -- Se sua tabela tive um campo IDENTITY(1,1) use ele 
   NUM varchar(10) null,
   CLIENTE varchar(10),
   EMISSAO date
)

insert into @Tabela1 (CLIENTE,EMISSAO ) values -- (NUM, CLIENTE,EMISSAO ) values -- aqui você faz o insert com o select da sua tabela 
--SELECT NUM, CLIENTE, EMISSAO
--  FROM PEDIDOS
-- WHERE CLIENTE = '06.028'
-- ORDER BY EMISSAO DESC

('06.028', '20160226'  ), 
('06.028', '20141030'   ), 
('06.028', '20141030'   ), 
('06.028', '20141030'  ), 
( '06.028', '20140612'  ), 
('05.127', '20160102'   ),  
('05.127', '20151225'  ),   
('05.127', '20151220'  ) ,
('05.127', '20151220'   ) ,
('05.127', '20151218'   ) ,
('05.127', '20151213'  ) 

select 
 tb.NUM, tb.CLIENTE, tb.EMISSAO,
case    
    when tb.CLIENTE = prev.CLIENTE then DATEDIFF (DAY , tb.EMISSAO, prev.EMISSAO) else 0 end TEMPO
 from @Tabela1 tb
join @Tabela1 prev 
ON prev.ROW = tb.ROW - 1

Answer 2

The expected result is in descending order of date and the TIME column is the difference, in days, between the current row and the previous row.

Here is the suggestion using LAG and DATEDIFF, as mentioned in the question statement:

-- código #1
set dateformat ymd;

with PARcliente as (
SELECT NUM, CLIENTE, EMISSAO,
       proxEMISSAO= lag (EMISSAO) over (partition by CLIENTE order by EMISSAO desc)
  from PEDIDOS
)
SELECT CLIENTE, EMISSAO, 
       datediff (day, EMISSAO, proxEMISSAO) as TEMPO
  from PARcliente
  order by CLIENTE, EMISSAO desc;

According to the data sample, the EMITTED column contains date but is stored as text, probably in the char column(8). To avoid implicit conversion errors the dateformat for ymd; details in the article The dangers of automatic data type conversion.

Answer 3

you can do this using a CTE:

DECLARE @PEDIDOS AS TABLE(
    NUM INT PRIMARY KEY IDENTITY,
    CLIENTE VARCHAR(6) NOT NULL,
    EMISSAO DATE NOT NULL
)

INSERT INTO @PEDIDOS VALUES
('06.028', '2016-02-26'),
('06.028', '2014-10-30'),
('06.028', '2014-10-30'),
('06.028', '2014-09-30'),
('06.028', '2014-06-12'),
('05.127', '2016-01-02'),
('05.127', '2015-12-25'),
('05.127', '2015-12-05');

WITH CTE_ROOT AS (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY CLIENTE ORDER BY EMISSAO DESC) AS ORDEM,
        NUM, 
        CLIENTE, 
        EMISSAO
    FROM @PEDIDOS
), CTE_LOOP AS (
    SELECT 
        CTE_ROOT.ORDEM,
        CTE_ROOT.NUM, 
        CTE_ROOT.CLIENTE, 
        CTE_ROOT.EMISSAO,
        DATEDIFF(DAY, CTE_ROOT.EMISSAO, SYSDATETIME()) AS TEMPO
    FROM CTE_ROOT
    WHERE CTE_ROOT.ORDEM = 1

    UNION ALL

    SELECT 
        CTE_ROOT.ORDEM,
        CTE_ROOT.NUM, 
        CTE_ROOT.CLIENTE, 
        CTE_ROOT.EMISSAO,
        DATEDIFF(DAY, CTE_ROOT.EMISSAO, CTE_LOOP.EMISSAO) AS TEMPO
    FROM CTE_LOOP
    JOIN CTE_ROOT ON CTE_LOOP.CLIENTE = CTE_ROOT.CLIENTE
    WHERE CTE_LOOP.ORDEM + 1 = CTE_ROOT.ORDEM
)

SELECT * 
FROM CTE_LOOP
ORDER BY CLIENTE, ORDEM