Teradata Reset row number according to conditions

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.

Teradata Reset row number according to conditions

Hello Experts

 

I am looking for consistent month since customer is available in database. The correct sequence column is output I am expecting. This is currently inserted manully to test result. if there is break in sequence for subsid then count should reset to 1 and then increase by 1 for consucative rows.

 

Please see fin_cs column where 6th row is incorrect. expected count is 3 but getting 6. any help is appreciated.

 

delta column in incorrect but please ignore it as it doesnt have any impact on issue i mentioned.

 

SUBSID

YEARMONTH

correct_sequence

DELTA

CALL_SEQUENCE

prev_cs

fin_cs

11112222

2018/01/01

1

-1

1

?

1

11112222

2018/02/01

2

1

2

1

2

11112222

2018/06/01

1

4

1

2

1

11112222

2018/08/01

1

2

1

1

1

11112222

2018/09/01

2

1

5

1

2

11112222

2018/10/01

3

1

6

5

6

11112222

2018/12/01

1

2

1

6

1

11112222

2019/01/01

2

89

1

1

1

11112222

2019/02/01

3

1

9

1

2

 

CREATE TABLE RN_TEST  ( SUBSID NUMBER, YEARMONTH NUMBER, CORRECT_SEQUENCE NUMBER);

INSERT INTO RN_TEST  VALUES(11112222,201801,1);
INSERT INTO RN_TEST  VALUES(11112222,201802,2);
INSERT INTO RN_TEST  VALUES(11112222,201806,1);
INSERT INTO RN_TEST  VALUES(11112222,201808,1);
INSERT INTO RN_TEST  VALUES(11112222,201809,2);
INSERT INTO RN_TEST  VALUES(11112222,201810,3);
INSERT INTO RN_TEST  VALUES(11112222,201812,1);
INSERT INTO RN_TEST  VALUES(11112222,201901,2);
INSERT INTO RN_TEST  VALUES(11112222,201902,3);

INSERT INTO RN_TEST  VALUES(33334444,201902,1);
INSERT INTO RN_TEST  VALUES(33334444,201903,2);
INSERT INTO RN_TEST  VALUES(33334444,201904,3);
INSERT INTO RN_TEST  VALUES(33334444,201905,4);
INSERT INTO RN_TEST  VALUES(33334444,201911,1);
INSERT INTO RN_TEST  VALUES(33334444,201912,2);
INSERT INTO RN_TEST  VALUES(33334444,202001,3);

INSERT INTO RN_TEST  VALUES(55556666,202001,1);
INSERT INTO RN_TEST  VALUES(55556666,202002,2);
INSERT INTO RN_TEST  VALUES(55556666,202004,1);
INSERT INTO RN_TEST  VALUES(55556666,202006,1);
INSERT INTO RN_TEST  VALUES(55556666,202008,1);
INSERT INTO RN_TEST  VALUES(55556666,202010,1);
INSERT INTO RN_TEST  VALUES(55556666,202012,1);

 

select b.*

,MIN(Call_Sequence) OVER ( PARTITION BY subsid  ORDER BY subsid, yearmonth desc ROWS BETWEEN 1 following AND 1 following ) as prev_cs

, case when delta=1 and abs(call_sequence-prev_cs)<>1 then prev_cs+1  else call_sequence end fin_cs

from(

SEL SUBSID, Yearmonth, correct_sequence, DELTA

,CASE WHEN  DELTA=-1 OR DELTA<>1  THEN 1 ELSE ROW_NUMBER() OVER ( PARTITION BY  subsid   ORDER BY SUBSID, Yearmonth  ASC ) END AS CALL_SEQUENCE

FROM (

SEL SUBSID , TO_DATE(substr(to_char(YearMonth),1,4) || substr(to_char(YearMonth),5,2)||'01', 'YYYYMMDD') YEARMONTH , correct_sequence

,CASE WHEN SUBSID=MAX(SUBSID)  OVER (ORDER BY SUBSID, Yearmonth  ASC   ROWS BETWEEN  1 PRECEDING   AND 1 PRECEDING)  

THEN Yearmonth -MAX(Yearmonth )  OVER ( ORDER BY SUBSID, Yearmonth ASC ROWS BETWEEN  1 PRECEDING   AND 1 PRECEDING)  ELSE -1 END AS DELTA

 FROM   mi_datalab.RN_Test_gk_1  ADV) a

) b

   ORDER BY subsid,yearmonth

Tags (2)

Accepted Solutions
Ambassador

Re: Teradata Reset row number according to conditions

Hi,

 

(Firstly, can I say "well done" for providing good information in your post. This makes it a lot easier for people to help you.).

 

I think the following will do what you want, the output column of interest is 'correct_sequence_calc'.

SEL dt1.*
, TO_DATE(TO_CHAR(YearMonth)||'01', 'YYYYMMDD') YEARMONTH_dt ,td_month_of_calendar(YEARMONTH_dt) AS month_num ,MIN(month_num) OVER(PARTITION BY subsid ORDER BY month_num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_month_number ,ROW_NUMBER() OVER(PARTITION BY subsid ORDER BY month_num RESET WHEN (month_num - prev_month_number) > 1) AS correct_sequence_calc FROM rn_test AS dt1;

You'll notice that the code is simplified a bit, I don't think there's a need for nested derived tables etc.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES 4
Ambassador

Re: Teradata Reset row number according to conditions

Hi,

 

(Firstly, can I say "well done" for providing good information in your post. This makes it a lot easier for people to help you.).

 

I think the following will do what you want, the output column of interest is 'correct_sequence_calc'.

SEL dt1.*
, TO_DATE(TO_CHAR(YearMonth)||'01', 'YYYYMMDD') YEARMONTH_dt ,td_month_of_calendar(YEARMONTH_dt) AS month_num ,MIN(month_num) OVER(PARTITION BY subsid ORDER BY month_num ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev_month_number ,ROW_NUMBER() OVER(PARTITION BY subsid ORDER BY month_num RESET WHEN (month_num - prev_month_number) > 1) AS correct_sequence_calc FROM rn_test AS dt1;

You'll notice that the code is simplified a bit, I don't think there's a need for nested derived tables etc.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com

Re: Teradata Reset row number according to conditions

Thank you so much! This is really simplified. Yesterday, I almost spent my entire day to find workaround and that too not working for months more than 4 months. This is simply awesome.

 

All I have to say is thank you! appreciate it!

Ambassador

Re: Teradata Reset row number according to conditions

This can be further simplified, at least in Explain (two vs three Stats steps), but you need a Derived Table :-)

 

SELECT dt.*
  ,Row_Number() Over(PARTITION BY subsid, grp ORDER BY yearmonth) AS rn
FROM 
 (
   SELECT t.*
     ,Trunc(YearMonth / 100) * 12 + YearMonth MOD 100
     -Row_Number() Over(PARTITION BY subsid ORDER BY YearMonth) AS grp
   FROM rn_test AS t
 ) AS dt;

The logic is based on a sequence without gaps and another with gaps. Calculating the difference results in the same (meaningless) value as long as there's no gap.

This is the result of the Derived Table:

SubsId    YearMonth   mon_num  rn     grp
11112222     201801     24217   1   24216
11112222     201802     24218   2   24216
11112222     201806     24222   3   24219
11112222     201808     24224   4   24220
11112222     201809     24225   5   24220
11112222     201810     24226   6   24220
11112222     201812     24228   7   24221
11112222     201901     24229   8   24221
11112222     201902     24230   9   24221
33334444     201902     24230   1   24229
33334444     201903     24231   2   24229
33334444     201904     24232   3   24229
33334444     201905     24233   4   24229
33334444     201911     24239   5   24234
33334444     201912     24240   6   24234
33334444     202001     24241   7   24234
55556666     202001     24241   1   24240
55556666     202002     24242   2   24240
55556666     202004     24244   3   24241
55556666     202006     24246   4   24242
55556666     202008     24248   5   24243
55556666     202010     24250   6   24244
55556666     202012     24252   7   24245

 

 

 

Teradata Employee

Re: Teradata Reset row number according to conditions

For fun this is the recursive approach.

It will probably perform way slower than previous code given by @DaveWellman and @dnoeth, so again, for fun.

 

with recursive cte_recurs (SUBSID, YEARMONTH, LVL) as
(
select SUBSID, YEARMONTH, 1
  from RN_TEST as t1
 where not exists (select null
                     from RN_TEST as t2
                    where t2.SUBSID = t1.SUBSID
                      and to_date(to_char(t2.YEARMONTH * 100 + 1), 'yyyymmdd') = to_date(to_char(t1.YEARMONTH * 100 + 1), 'yyyymmdd') - interval '1' month)
 union all
select rnt.SUBSID, rnt.YEARMONTH, cte.LVL + 1
  from cte_recurs as cte
  join RN_TEST    as rnt  on rnt.SUBSID = cte.SUBSID
                         and to_date(to_char(rnt.YEARMONTH * 100 + 1), 'yyyymmdd') = to_date(to_char(cte.YEARMONTH * 100 + 1), 'yyyymmdd') + interval '1' month
)
  select SUBSID, YEARMONTH, LVL
    from cte_recurs
order by SUBSID, YEARMONTH;