Teradata Count number of rows using a window function excluding a duplicate condition

Question

I have the following data in my Teradata tables and I am trying to get the number of rows as 'TENURE' based on some conditions

Have

  Id    MONTH            ORDERNO       STATUS
  101   2022-01-31        105           00
  101   2022-02-28        105           00
  101   2022-03-31        106           00
  101   2022-04-30        106           07
  101   2022-05-31        106           07
  102   2022-01-01        105           00
  102   2022-02-28        105           00
  102   2022-03-31        105           07
  102   2022-04-28        105           07

Want

  Id     TENURE
  101      4
  102      3

I have to exclude the 2nd appearance of STATUS='07' and count the number of months

I am using the following window function to get the number of rows but I don't know how to add status='07' filter

 SELECT id,
      COUNT(*) OVER (PARTITION BY id ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND 
  UNBOUNDED FOLLOWING) AS TENURE FROM Have;

I got the following instead of 4 and 3 respectively

  Id     TENURE
  101      5
  102      4

*I have to exclude the 2nd appearance of STATUS='07'* based on which rules? — dnoeth, Apr 29 '23 at 09:24
If you want an aggregate result, this should work: `count(case when STATUS <> '07' then "month" end) + max(case when STATUS = '07' then 1 else 0 end)` — dnoeth, Apr 29 '23 at 14:35

score 0 · Answer 1 · edited Apr 29 '23 at 14:33

Something is incorrect with your example (BTW, "MONTH" should be in Quotes as it is a reserved word).

INSERT INTO DWHPRO.HAVE (Id, "MONTH", ORDERNO, STATUS) VALUES
(101, '2022-01-31', 105, '00'),
(101, '2022-02-28', 105, '00'),
(101, '2022-03-31', 106, '00'),
(101, '2022-04-30', 106, '07'),
(101, '2022-05-31', 106, '07'),
(102, '2022-01-01', 105, '00'),
(102, '2022-02-28', 105, '00'),
(102, '2022-03-31', 105, '07'),
(102, '2022-04-28', 105, '07');


SELECT id, COUNT(*) OVER (PARTITION BY id ORDER BY MONTH ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS TENURE 
FROM Have;

Your query delivers:

I assume you want to count per month?

score 0 · Answer 2 · answered Apr 30 '23 at 10:08

You can achieve it by adding the sum of all records with statuses other than 07 + 1 (if one or more than one record with status 07 exists, otherwise 0):

select id, count(case when STATUS <> '07' then 1 end) + max(case when STATUS = '07' then 1 else 0 end) as TENURE
From HAVE
group by id

Result :

Id  TENURE
101 4
102 3

Teradata Count number of rows using a window function excluding a duplicate condition

2 Answers2