Difference of days between records and display only results > that 14

Asked

Viewed 642 times

1

I have the following appointment:

 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

She 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 the diferença for maior que 14

BD: SQL SERVER

According to @GOKU Ssjgod’s tip

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’s doing the math, only she’s calculating the difference between 1 and the last in the case disregarding 2. I need you to calculate the difference between all records something like DATEDIFF(currentdata_ - previous date)

sql sql-server query

  • Possible duplicate of your previous Difference of hours query sql server? I think the fact that I accepted an answer that doesn’t answer what I was asked doesn’t help much in that case, it’s important to actually solve every step. Solved that, make a difference WHERE > 14 is the least.

  • In fact in that there I had the status 1 and 4 where I worked with them and it worked out, in this case it’s all the same where the only difference are the dates,.

  • it would be good to define the db used in the question, can make difference to compare between lines.

  • By the way, I can’t see how DATEDIFF could solve that question, and they are different lines (it’s the same problem as this one). If you did, it would be nice to post an answer on that one showing how you did it, it would help other people.

  • Solution posted, please check and return me your opinion.

  • If it were at that, it would be the case for a HAVING hours > 14 at the end, pq is grouping. If it’s between lines, without grouping, you’ll need some kind of variable. In Mysql it would be @var = something (to save the previous line for comparison). In T-SQL I don’t know how it is.

  • 1

    Blza, I’m going to do a little research on what you said, in case you get the solution. Thank you !

Show 2 more comments

1 answer

1


Follows 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;

Browser other questions tagged sql sql-server query

You are not signed in. Login or sign up in order to post.