How to counter "blocks" in Sql Server

Asked

Viewed 894 times

2

I would like to know how an accountant would look in an SQL query for the following situation:

I have the columns matricula, date and time worked where I would like to create another column with the counter where would be a sequence only in the zeros and the different zeroes.

Table structure

MATRICULA / DATA       / HORA TRABALHADA

005770    | 21/04/2015 | 0    | 1

005770    | 22/04/2015 | 703  | 1

005770    | 23/04/2015 | 485  | 2

005770    | 24/04/2015 | 517  | 3

005770    | 25/04/2015 | 0    | 1

005770    | 26/04/2015 | 0    | 2
sql-server

  • Could you give an example of how you want it to look?

  • in the 5th registration would not be 2 and dps 3?

  • or you want to reset every time you change the sequence?

  • 1

    good afternoon Maicon, would have to reset the sequence. the institution of this consultation is to check if the employee has worked hour for seven days or more.

1 answer

2


I’ve had some problems with changing values according to the sequence, you’ll need the function LAG, it serves to return the value of the previous line. And then partitions its values.

with f1 as
(
  SELECT matricula, data, hora, 
         lag(hora) over (order by matricula) anterior
  FROM funcionario
),
f2 as
(
  SELECT matricula, data, hora, 
         sum(case when (hora = 0 and anterior = 0) 
                    or (hora <> 0 and anterior <> 0)
             then 0 else 1 end) 
          over(order by matricula rows unbounded preceding) as mudou
  FROM f1
)
select matricula,
       data,
       hora,
       row_number() over(partition by mudou order by matricula) as seq
from f2

Sqlfiddle Demo #1

Exit:

matricula   data        hora    seq
005770      2015-04-21  0       1
005770      2015-04-22  703     1
005770      2015-04-23  485     2
005770      2015-04-24  517     3
005770      2015-04-25  0       1
005770      2015-04-26  0       2



Explanation:

1st SELECT:

SELECT matricula, data, hora, 
         lag(hora) over (order by matricula) anterior
FROM funcionario

Will return a select with the values of the current line and the previous time.

inserir a descrição da imagem aqui


2nd SELECT:

SELECT matricula, data, hora, 
       sum(case when (hora = 0 and anterior = 0) 
                    or (hora <> 0 and anterior <> 0)
             then 0 else 1 end) 
          over(order by matricula rows unbounded preceding) as mudou
FROM f1

In this I group the values when they are current and previous hours = 0 or else they are different from 0.

inserir a descrição da imagem aqui


3rd SELECT:

select matricula,
       data,
       hora,
       row_number() over(partition by mudou order by matricula) as seq
from f2

I use the row_number to count the lines, HOWEVER particionado by the groupings made previously.

inserir a descrição da imagem aqui

Stay tuned! If you want to change the ordenação need to change at all OVER of all SELECT.

Using only row_number (MS Sql 2008)

WITH CTE_RN AS 
(
    SELECT
        *,
        ROW_NUMBER() OVER(PARTITION BY CHAPA ORDER BY CHAPA, DATA) AS RN
    FROM AAFHTFUN
WHERE        DATA  >= '04-01-2015'
AND          DATA  <= '05-20-2015'
)

SELECT
    t.CHAPA,
    t.DATA,
    t.HTRAB,
    CASE WHEN t.HTRAB = 0 THEN 1
         ELSE t.RN - COALESCE((SELECT TOP(1) s.RN
                      FROM CTE_RN AS s
                      WHERE s.HTRAB = 0 AND s.rn < t.RN
                            AND s.CHAPA = t.CHAPA
                      ORDER BY RN DESC), 0)
    END AS seq
FROM CTE_RN AS t

Sqlfiddle Demo #2

Browser other questions tagged sql-server

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