Query condition to display only records that contain the same 3x consecutive number

Asked

Viewed 167 times

0

I have the following appointment:

with
Consulta as (
 SELECT 
    V.CHAPA,
     F.NOME,
    V.DATA,
        F.FILIAL,
    DATENAME(dw,V.DATA) AS DIA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
      FROM
            V_BATIDASANTIGAS AS V
             LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
        WHERE

            DATEPART(dw,V.DATA) = 1
        AND BATIDA IS NOT NULL 
        AND V.CODCOLIGADA = 1 
        AND  YEAR ( V.DATA ) = 2016 
         GROUP BY V.CHAPA,
                  F.NOME,
                  F.FILIAL,
                  V.DATA 
),
ConsultaSeq as (
SELECT *, Seq= row_number() over (partition by CHAPA order by DATA)
  from Consulta
)
SELECT 
C1.CHAPA, 
 C1.NOME, 
 C1.FILIAL,
    C1.DATA, 
    C2.DATA, 
    DateDiff(day, C1.DATA, C2.DATA)as DIAS,
    '04 - MAIS DE 2 DOMINGOS SEM FOLGA ' AS DESCRICAO
  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) > 1
        GROUP BY  C1.CHAPA,
         C1.NOME, 
          C1.FILIAL,
            C1.DATA, 
             C2.DATA

He returns to me:

1234    JOAO DA SILVA   1   2016-01-10 00:00:00.000 2016-01-17 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-01-17 00:00:00.000 2016-01-31 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-01-31 00:00:00.000 2016-02-07 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-02-07 00:00:00.000 2016-02-21 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-02-21 00:00:00.000 2016-02-28 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-02-28 00:00:00.000 2016-03-13 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-03-13 00:00:00.000 2016-03-20 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-03-20 00:00:00.000 2016-05-08 00:00:00.000 49  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-05-08 00:00:00.000 2016-05-29 00:00:00.000 21  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-05-29 00:00:00.000 2016-06-05 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-06-05 00:00:00.000 2016-06-19 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-06-19 00:00:00.000 2016-06-26 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-06-26 00:00:00.000 2016-07-17 00:00:00.000 21  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-07-17 00:00:00.000 2016-07-24 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-07-24 00:00:00.000 2016-08-07 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-08-07 00:00:00.000 2016-08-14 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-08-14 00:00:00.000 2016-08-28 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-08-28 00:00:00.000 2016-09-04 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-09-04 00:00:00.000 2016-09-25 00:00:00.000 21  04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-09-25 00:00:00.000 2016-10-02 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-10-02 00:00:00.000 2016-10-16 00:00:00.000 14  04 - MAIS DE 2 DOMINGOS SEM FOLGA

What I need:

Based on the month of the date only the results that have 7 3x consecutive are displayed.

Example:

1234    JOAO DA SILVA   1   2016-01-10 00:00:00.000 2016-01-17 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-01-17 00:00:00.000 2016-01-31 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA 
1234    JOAO DA SILVA   1   2016-01-31 00:00:00.000 2016-02-07 00:00:00.000 7   04 - MAIS DE 2 DOMINGOS SEM FOLGA

How could I parameterize this ?

NOTE: I can also read and save the new result in a table,I say because if you simpler meets me too.

sql-server

  • Otácio, can there be more than one employee with the same PLAQUE value, if they are from different branches? The example does not match the request; you could check?

  • No, the plate is like the employee’s primary key.

  • Next, which logic is following because returned empty, just so I understand the code and can validate

  • Otácio, the logic is as follows: considering the objective "MORE THAN 2 SUNDAYS WITHOUT SLACK", for each line existing in #Tabbatidas (only Sundays), it is verified if there are beats in the next two Sundays. It is in the WHERE clause: +7 days and + 14 days

  • I got it, I’ll validate it and I’ll get back to you today,

1 answer

1


Octavio, here’s a possibility: 

-- código #1 v5
IF Object_ID('tempDB..#TabBatidas', 'U') is not null
  DROP TABLE #TabBatidas;

--
set DATEFIRST 7;  -- domingo

--
with
Consulta as (
SELECT 
    V.CHAPA,
    F.NOME,
    V.DATA,
    F.FILIAL, 
    DATENAME(dw,V.DATA) AS DIA,
    MAX(V.SEQUENCIALBATIDA) AS BATIDA
  FROM
            V_BATIDASANTIGAS AS V
            LEFT JOIN V_DADOSFUNC  AS F ON V.CHAPA = F.CHAPA
  WHERE
        DATEPART(dw,V.DATA) = 1  -- domingo
        AND BATIDA IS NOT NULL 
        AND V.CODCOLIGADA = 1 
        AND  YEAR ( V.DATA ) = 2016 
  GROUP BY V.CHAPA,
           F.NOME,
           F.FILIAL,
           V.DATA 
)
SELECT * 
  into #TabBatidas
  from Consulta;

CREATE clustered INDEX I1_TB on #TabBatidas (CHAPA, DATA);

--
SELECT T1.CHAPA, T1.NOME, T1.FILIAL, 
      '04 - MAIS DE 2 DOMINGOS SEM FOLGA' AS DESCRIÇÃO,
       Cast(T1.DATA as date) as [DATA 1],
       Cast(T2.DATA as date) as [DATA 2],
       Cast(T3.DATA as date) as [DATA 3]
  from #TabBatidas as T1
       inner join #TabBatidas as T2 on T2.CHAPA = T1.CHAPA
       inner join #TabBatidas as T3 on T3.CHAPA = T1.CHAPA
  where T2.DATA = DateAdd(day, +7, T1.DATA)
        and T3.DATA = DateAdd(day, +14, T1.DATA);    

--
IF Object_ID('tempDB..#TabBatidas', 'U') is not null
  DROP TABLE #TabBatidas;
go

  • Validated, perfect, Thank you !

Browser other questions tagged sql-server

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