How to do counter in "blocks" in Sql Server

Navigation
2

I would like to know how a counter 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 ones of zeros.

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
    
asked by anonymous 19.06.2015 / 19:13

1 answer

2

I've already had some problems with changing values according to the sequence, you will need the function LAG , it will return the value of the previous line. And then partition your 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

Output: 

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

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


2ndSELECT:

SELECTmatricula,data,hora,sum(casewhen(hora=0andanterior=0)or(hora<>0andanterior<>0)then0else1end)over(orderbymatricularowsunboundedpreceding)asmudouFROMf1

InthisIgroupthevalueswhentheyarecurrentandprevioushours=0ortheyaredifferentfrom0.


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, BY particionado by the groupings done previously.



Staytuned!IfyouwanttochangeordenaçãoyouneedtochangeallOVERofallSELECT.

Usingonlyrow_number(MSSql2008)

WITHCTE_RNAS(SELECT*,ROW_NUMBER()OVER(PARTITIONBYCHAPAORDERBYCHAPA,DATA)ASRNFROMAAFHTFUNWHEREDATA>='04-01-2015'ANDDATA<='05-20-2015')SELECTt.CHAPA,t.DATA,t.HTRAB,CASEWHENt.HTRAB=0THEN1ELSEt.RN-COALESCE((SELECTTOP(1)s.RNFROMCTE_RNASsWHEREs.HTRAB=0ANDs.rn<t.RNANDs.CHAPA=t.CHAPAORDERBYRNDESC),0)ENDASseqFROMCTE_RNASt

SqlFiddle Demo # 2

    
19.06.2015 / 19:53
Android xml lines (Eclipse) How to create a function to clean the group of a radiobutton?