Fill in values from other data in the same column (Teradata)

Question

I have a table which is a result of a query(it's a big one!) which looks like this.

Table is trying to count a set(seq_num 1,2..) of names in each id by type but it leave zeroes when different type appears in the same id

I would like to get a result which looks like this instead.

Can't use subquery or max because that would require group by which would be a pain because the query is already too complicated.

Would really appreciate some help here. Thanks!

Query code for those extra 4 columns on the far right:

CASE WHEN (TYPE = 'E') THEN (DENSE_RANK() OVER (PARTITION BY ID,TYPE ORDER BY NAME) + DENSE_RANK() OVER (PARTITION BY ID, TYPE ORDER BY NAME DESC) - 1) ELSE 0 END AS NC_E,

CASE WHEN (TYPE = 'M') THEN (DENSE_RANK() OVER (PARTITION BY ID,TYPE ORDER BY NAME) + DENSE_RANK() OVER (PARTITION BY ID, TYPE ORDER BY NAME DESC) - 1) ELSE 0 END AS NC_M,

CASE WHEN (TYPE = 'D') THEN (DENSE_RANK() OVER (PARTITION BY ID,TYPE ORDER BY NAME) + DENSE_RANK() OVER (PARTITION BY ID, TYPE ORDER BY NAME DESC) - 1) ELSE 0 END AS NC_D,

CASE WHEN (TYPE = 'C') THEN (DENSE_RANK() OVER (PARTITION BY ID,TYPE ORDER BY NAME) + DENSE_RANK() OVER (PARTITION BY ID, TYPE ORDER BY NAME DESC) - 1) ELSE 0 END AS NC_C

NOTE: I have other IDs in which the TYPE doesn't change and that's when it works fine and I can understand why that is. The problem is with the info being quite diverse in each ID.

Answer 1

You want a COUNT(DISTINCT Name) OVER (PARTITION BY ID, TYPE), which is not supported in Teradata.

The most efficient way will be a nested OLAP-function, which will result in two STAT-steps, like your current solution, so this shouldn't be less efficient:

SELECT dt.*,
    Max(CASE WHEN type = 'E' THEN nc END) Over (PARTITION BY id) AS NC_E
   ,Max(CASE WHEN type = 'M' THEN nc END) Over (PARTITION BY id) AS NC_M 
   ,Max(CASE WHEN type = 'D' THEN nc END) Over (PARTITION BY id) AS NC_D 
   ,Max(CASE WHEN type = 'C' THEN nc END) Over (PARTITION BY id) AS NC_C 
FROM
 ( 
   SELECT ....
      Dense_Rank() Over (PARTITION BY ID, TYPE_ ORDER BY NAME) AS nc
   FROM ...
 ) AS dt