Search code examples
sqldb2db2-400db2-luw

Repeat the Max count of LEAST(DENSE_RANK() OVER (PARTITION BY))

How can I rewrite the sql below to repeat the max number of count for each group Like the picture as Desired Result column?

LEAST(DENSE_RANK() OVER (PARTITION BY GP, DATEDIFF  ORDER BY  YRMO ASC), 12) AS ACTIVE_MOS

enter image description here

GP  YRMO    DATEDIFF    ACTIVE_MOS  DESIRED RESULT
54  202012  0   1   12
54  202101  0   2   12
54  202102  0   3   12
54  202103  0   4   12
54  202104  0   5   12
54  202105  0   6   12
54  202106  0   7   12
54  202107  0   8   12
54  202108  0   9   12
54  202109  0   10  12
54  202110  0   11  12
54  202111  0   12  12
54  202112  0   12  12
54  202201  0   12  12
54  202202  0   12  12
54  202203  0   12  12
54  202204  0   12  12
54  202205  0   12  12
54  202206  0   12  12
54  202207  0   12  12
54  202208  0   12  12
54  202209  0   12  12
54  202210  0   12  12
54  202211  0   12  12
54  202310  1   1   4
54  202311  1   2   4
54  202312  1   3   4
54  202401  1   4   4

Solution

  • Try this.

    WITH T (GP, YRMO, DATEDIFF) AS
(
VALUES
  (54, 202012, 0)
, (54, 202101, 0)
, (54, 202102, 0)
, (54, 202103, 0)
, (54, 202104, 0)
, (54, 202105, 0)
, (54, 202106, 0)
, (54, 202107, 0)
, (54, 202108, 0)
, (54, 202109, 0)
, (54, 202110, 0)
, (54, 202111, 0)
, (54, 202112, 0)
, (54, 202201, 0)
, (54, 202202, 0)
, (54, 202203, 0)
, (54, 202204, 0)
, (54, 202205, 0)
, (54, 202206, 0)
, (54, 202207, 0)
, (54, 202208, 0)
, (54, 202209, 0)
, (54, 202210, 0)
, (54, 202211, 0)
, (54, 202310, 1)
, (54, 202311, 1)
, (54, 202312, 1)
, (54, 202401, 1)
)
SELECT 
  T.*
, MAX (ACTIVE_MOS) OVER (PARTITION BY GP, DATEDIFF) AS DESIRED_RESULT
FROM
(
SELECT 
  T.*
, LEAST (DENSE_RANK() OVER (PARTITION BY GP, DATEDIFF  ORDER BY  YRMO ASC), 12) AS ACTIVE_MOS 
FROM T
) T
    GP YRMO DATEDIFF ACTIVE_MOS DESIRED_RESULT
    54 202012 0 1 12
    54 202101 0 2 12
    54 202102 0 3 12
    54 202103 0 4 12
    54 202104 0 5 12
    54 202105 0 6 12
    54 202106 0 7 12
    54 202107 0 8 12
    54 202108 0 9 12
    54 202109 0 10 12
    54 202110 0 11 12
    54 202111 0 12 12
    54 202112 0 12 12
    54 202201 0 12 12
    54 202202 0 12 12
    54 202203 0 12 12
    54 202204 0 12 12
    54 202205 0 12 12
    54 202206 0 12 12
    54 202207 0 12 12
    54 202208 0 12 12
    54 202209 0 12 12
    54 202210 0 12 12
    54 202211 0 12 12
    54 202310 1 1 4
    54 202311 1 2 4
    54 202312 1 3 4
    54 202401 1 4 4

    fiddle

    Note, that you may omit the subselect in DB2 for LUW (but not in DB2 for IBM i), if you don't need the ACTIVE_MOS column in the result with the following expression.

    MAX 
(
  LEAST (DENSE_RANK() OVER (PARTITION BY GP, DATEDIFF  ORDER BY  YRMO ASC), 12)
) OVER (PARTITION BY GP, DATEDIFF) AS DESIRED_RESULT