Days difference between records and display only the results that 14

Question

Days difference between records and display only the results that 14

Navigation

#1 by (1 votes)

1

I have the following query:

 SELECT 
    V.CHAPA,
    F.NOME,
    V.DATA,
    DATENAME(dw,V.DATA) AS DIA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 123
        AND DATEPART(dw,V.DATA) = 1
        AND BATIDA IS NOT NULL 
        AND V.CODCOLIGADA = 1 
        AND  YEAR ( V.DATA ) = 2016 
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA 
          ORDER BY V.DATA ASC

It returns:

123 JOAO DA SILVA   2016-09-04 00:00:00.000 Sunday  2
123 JOAO DA SILVA   2016-09-11 00:00:00.000 Sunday  2
123 JOAO DA SILVA   2016-09-25 00:00:00.000 Sunday  2

I need to know the diferença de dias entre cada registro and only display the records where diferença is maior que 14

BD: SQL SERVER

According to the tip of @GOKU SSJGod

123 JOAO DA SILVA   2016-09-04 00:00:00.000 Sunday  2
>>>>> 123 JOAO DA SILVA   2016-09-11 00:00:00.000 Sunday  2
123 JOAO DA SILVA   2016-09-25 00:00:00.000 Sunday  2

She is doing the calculation, only she is calculating the difference between 1 and the last one in the case disregarding the 2. I need you to calculate the difference between all records something like DATEDIFF (date_atual - Previous_Date)

sql sql-server query

asked by anonymous 19.10.2016 / 23:24

1 answer

** optional kwargs with default Is this object only available when I give dd ()?

score 1 · Accepted Answer

Follow this solution:

-- código #1
with
Consulta as (
 SELECT 
    V.CHAPA,
    F.NOME,
    V.DATA,
    DATENAME(dw,V.DATA) AS DIA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            ARELBATIDATRANSITOVIEW AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE
            V.CHAPA = 123
        AND DATEPART(dw,V.DATA) = 1
        AND BATIDA IS NOT NULL 
        AND V.CODCOLIGADA = 1 
        AND  YEAR ( V.DATA ) = 2016 
        AND MONTH ( V.DATA ) = 9
         GROUP BY V.CHAPA,
                  F.NOME,
                  V.DATA 
),
ConsultaSeq as (
SELECT *, Seq= row_number() over (partition by CHAPA order by DATA)
  from Consulta
)
SELECT C1.CHAPA, C1.NOME, C1.DATA, C2.DATA, DateDiff(day, C1.DATA, C2.DATA) as Dias
  from ConsultaSeq as C1
       left join ConsultaSeq as C2 on C1.CHAPA = C2.CHAPA and C1.Seq = C2.Seq -1
  where DateDiff(day, C1.DATA, C2.DATA) > 14;