CODEWITHSUNDEEP
sqlteradata
Jerrycheen
Jerrycheen

Reputation: 23

How to create an incremental counter in a table

Given a table contains these data

Enrolid  Dtstart       Preceding Dtend        
001      2000-02-01    2000-01-13     
001      2000-03-01    2000-02-29
001      2000-04-01    2000-03-31
001      2000-07-01    2000-04-30
001      2000-09-01    2000-07-31
002      2000-03-01    2000-02-29
002      2000-04-01    2000-03-31
002      2000-07-01    2000-04-30
002      2000-09-01    2000-07-31

How to generate a new field as a counter, starting from 1 when Dtstart - Preceding Dtend > 31, counter = counter + 1 else no change. And ideally, it could be reset by EnrolId.

So the result will be :

Enrolid  Dtstart       Preceding Dtend      Counter  
001      2000-02-01    2000-01-13           1
001      2000-03-01    2000-02-29           1
001      2000-04-01    2000-03-31           1
001      2000-07-01    2000-04-30           2
001      2000-09-01    2000-07-31           3
002      2000-03-01    2000-02-29           1
002      2000-04-01    2000-03-31           1
002      2000-07-01    2000-04-30           2
002      2000-09-01    2000-07-31           3

I tried to use a stored procedure, and run a loop as below"

for loopvar as cur1 cursor for 
  select enrolid, dtstart, dtstart-preceding_dtend as gap 
  from dc_rwd_omop.temp_observation_period 
  order by enrolid, dtstart

    do 
        if (loopvar.gap is not null and loopvar.gap > 31) then

            set counter = counter +1; 
        end if;
end for;

However, it is not efficient as the table contains billions of records.

Any idea to have an efficient way?

Upvotes: 1

Views: 134

Answers (1)

David דודו Markovitz
David דודו Markovitz

Reputation: 44941

select      Enrolid,Dtstart,"Preceding Dtend"   

           ,count(case when Dtstart - "Preceding Dtend" > 31 then 1 end) over
            (
                partition by Enrolid
                order by     Dtstart
                rows         unbounded preceding
            ) 
          + case    
                when    first_value (Dtstart - "Preceding Dtend") over
                        (
                            partition by Enrolid
                            order by     Dtstart
                            rows         unbounded preceding
                        ) > 31 
                then    0
                else    1
            end             as Counter                    

from        t

Upvotes: 2

Related Questions