Difference between SQL SERVER dates

Question

I have a question on sql server.

Based on the table below, how can I catch the difference in days which takes to go from one phase to the other of the same client, ie difference phase to phase.

Example:

• The difference between the id 2 and 1 are 9 days;

• Between the id 3 and 2 are 10 days;

Answer 1

You can use the function LEAD that accesses the subsequent line data, that is, this function used in the SELECT server to compare the current line with the values of the next line

SELECT DATEDIFF(day, data , data_next)  AS sub
FROM (
  SELECT [data], lead([data],1,0) over (order by id) as data_next FROM test1) 
AS T

Sqlfiddle example

_References

• SQL 2012 LEAD and LAG functions
• LEAD (Transact-SQL)

Answer 2

Using the Datediff() you can catch the difference between dates.

The Select below solves your problem for a specific case.

Sqlfiddle - Online example:

SELECT 
  DATEDIFF(
    day 
    , (SELECT Data FROM Teste WHERE ID =1) 
    , (SELECT Data FROM Teste WHERE ID =2)
  )  AS DiferencaEmDias

In case I wanted to leave the dynamic solution , you can create a procedure as in the example below, in the case it takes as parameter two Id.

Example:

CREATE PROCEDURE DiferencaFases 
    @idInicial INT
    , @idFinal INT
AS
  SELECT 
  DATEDIFF(
    day 
    , (SELECT Data FROM Teste WHERE ID =@idInicial) 
    , (SELECT Data FROM Teste WHERE ID =@idFinal)
  )  AS DiferencaEmDias
GO

Answer 3

To avoid using subselect in clause select, you can cross table data using one ranking per customer.

Thus:

SELECT 
    SOURCE.ID,
    SOURCE.CLIENTE,
    SOURCE.FASE,
    SOURCE.DATA,
    DATEDIFF(DAY, QUERY.DATA, SOURCE.DATA) DIAS
FROM (SELECT RANK() OVER (PARTITION BY CLIENTE ORDER BY FASE) AS RANKING, 
             T.ID, 
             T.CLIENTE, 
             T.FASE, 
             T.DATA FROM Teste T) SOURCE
     LEFT JOIN (SELECT RANK() OVER (PARTITION BY CLIENTE ORDER BY CLIENTE, FASE) AS RANKING, 
                       T.CLIENTE,
                       T.FASE, 
                       T.DATA FROM Teste T) QUERY ON QUERY.CLIENTE = SOURCE.CLIENTE AND (QUERY.RANKING = SOURCE.RANKING - 1)

Depending on the actual scenario where you intend to apply performance gain while avoiding subselect can be quite interesting.

This example is available in SQL Fiddle