Query with condition of displaying only records that contain the same consecutive 3x number

Question

Query with condition of displaying only records that contain the same consecutive 3x number

Navigation

#1 by (1 votes)

0

I have the following query:

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

It returns 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 results that have the number 7 consecutive 3x 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 do a reading and save the new result in a table, I say then if you simpler answer me too.

sql-server

asked by anonymous 23.10.2016 / 23:55

1 answer

Connection Problems pgsql in Laravel 5.1 What is Serialize and Deserialize for? [duplicate]

score 1 · Accepted Answer

Otácio, 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