Difference between dates in several lines

Navigation
1

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

SELECT 

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

Result 

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

Note: the ROW column is only for displaying the line that I am referencing in the description (- = >) next to it.

When changing the client, it stops using the date of the previous client and starts over again.

    
asked by anonymous 02.03.2016 / 19:20

3 answers

1

Try to do this as follows 

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

    
04.03.2016 / 15:43
0

Something like this 

SELECT DATADIFF(T2.DATA - T1.DATA)
FROM (SELECT *
      FROM TABELA) T1,
    (SELECT *
      FROM TABELA) T2
WHERE (T1.ROW+1 = T2.ROW) /*FAZER UM "JOIN" COM O ANTERIOR*/
    
04.03.2016 / 14:44
0

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
    
04.03.2016 / 18:02
how to show data from a sharedPreferences in a ListActivity A potentially dangerous Request.Path value was detected from the client