Oracle Sequential assignment of alphabets based on date column-CodePudding

Table 1:

|Trans|Inc|Date|Status|
-----------------------
|1|1|01/01/2022|null
|5|1|20/01/2022|null
|3|1|03/01/2022|null
|11|2|01/01/2022|null
|3|2|13/12/2021|null

result should be

|Trans|Inc|Date|Status|
-----------------------
|1|1|01/01/2022|A
|5|1|20/01/2022|C
|3|1|03/01/2022|B
|11|2|01/01/2022|B
|3|2|13/12/2021|A

Logic: Table1 has 4 columns, and status column should be updated on below terms: 1 Inc can have multiple trans attached to it. So status should be updated on the basis Date column. ex. for Inc 1, we have 4 trans(so there will be status A,B,C,D) and Tran 1 date 01/01/2022 so status is 'A', second record is for inc 1 tran 3, date is 03/01/2022, so the Status is B. Basically we need to assign status alphabetically based on Date column.

Need help for this.

CodePudding user response：

You could use ROW_NUMBER here:

SELECT Trans, Inc, Date,
       SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',
              ROW_NUMBER() OVER (PARTITION BY Inc ORDER BY Date), 1) AS Status
FROM yourTable;

The trick here is to take a 1-substring of the alphabets depending on the row number position of each record, within each group of company records.

CodePudding user response：

You can use:

SELECT Trans,
       Inc,
       "DATE",
       CASE
       WHEN rn < 26      THEN    CHR(65   rn)
       WHEN rn < 27 * 26 THEN    CHR(65   MOD(FLOOR((rn-26)/POWER(26,1)),26))
                              || CHR(65   MOD(FLOOR((rn-26)/POWER(26,0)),26))
                         ELSE    CHR(65   MOD(FLOOR((rn-27*26)/POWER(26,2)),26))
                              || CHR(65   MOD(FLOOR((rn-27*26)/POWER(26,1)),26))
                              || CHR(65   MOD(FLOOR((rn-27*26)/POWER(26,0)),26))
       END AS status
FROM   (
  SELECT Trans,
         Inc,
         "DATE",
         ROW_NUMBER() OVER (PARTITION BY Inc ORDER BY "DATE") - 1 AS rn
  FROM   table_name t
);

Which, for the sample data:

CREATE TABLE table_name (Trans, Inc, "DATE", Status) AS
SELECT  1, 1, DATE '2022-01-01', CAST(NULL AS VARCHAR2(3)) FROM DUAL UNION ALL
SELECT  5, 1, DATE '2022-01-20', CAST(NULL AS VARCHAR2(3)) FROM DUAL UNION ALL
SELECT  3, 1, DATE '2022-01-03', CAST(NULL AS VARCHAR2(3)) FROM DUAL UNION ALL
SELECT 11, 2, DATE '2022-01-01', CAST(NULL AS VARCHAR2(3)) FROM DUAL UNION ALL
SELECT  3, 2, DATE '2021-12-13', CAST(NULL AS VARCHAR2(3)) FROM DUAL;

This will output statuses from A through Z then AA through to ZZ then AAA through to ZZZ for each INC value.

Outputs:

TRANS INC DATE STATUS

1 1 2022-01-01 00:00:00 A

3 1 2022-01-03 00:00:00 B

5 1 2022-01-20 00:00:00 C

3 2 2021-12-13 00:00:00 A

11 2 2022-01-01 00:00:00 B

TRANS	INC	DATE	STATUS
1	1	2022-01-01 00:00:00	A
3	1	2022-01-03 00:00:00	B
5	1	2022-01-20 00:00:00	C
3	2	2021-12-13 00:00:00	A
11	2	2022-01-01 00:00:00	B

If you want to update the status values then you can MERGE using the ROWID pseudo-column to correlate:

MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         CASE
         WHEN rn < 26      THEN    CHR(65   rn)
         WHEN rn < 27 * 26 THEN    CHR(65   MOD(FLOOR((rn-26)/POWER(26,1)),26))
                                || CHR(65   MOD(FLOOR((rn-26)/POWER(26,0)),26))
                           ELSE    CHR(65   MOD(FLOOR((rn-27*26)/POWER(26,2)),26))
                                || CHR(65   MOD(FLOOR((rn-27*26)/POWER(26,1)),26))
                                || CHR(65   MOD(FLOOR((rn-27*26)/POWER(26,0)),26))
         END AS status
  FROM   (
    SELECT ROW_NUMBER() OVER (PARTITION BY Inc ORDER BY "DATE") - 1 AS rn
    FROM   table_name t
  )
) src
ON (src.rid = dst.ROWID)
WHEN MATCHED THEN
  UPDATE
  SET status = src.status;

db<>fiddle here