Assign min value or max value based on a value change-CodePudding

HAVE

ID  cd dt       ct_vl
1   A  20210101 0
1   B  20210201 0 
1   A  20210301 0
2   A  20210101 0
2   B  20210201 1
3   C  20210101 0
3   V  20210201 1
3   C  20210301 0
3   C  20210401 0

WANT (Get min date when ct_val within the ID group is 0 across that ID group OR get max date within the ID group where ct_val is not 0 (that is it may contain a 1) across that ID group). For instance, if the ct_val within an ID group is 1, pick the maximum dt where ct_val within that group is 1

ID  cd dt       ct_vl final_dt
1   A  20210101 0      20210101
1   B  20210201 0      20210101
1   A  20210301 0      20210101 
2   A  20210101 0      20210201
2   B  20210201 1      20210201
3   C  20210101 0      20210301
3   V  20210201 1      20210301 
3   C  20210301 1      20210301
3   C  20210401 0      20210301

CodePudding user response：

You can try to use MAX condition aggragte window function with FIRST_VALUE window function to get your logic.

Query 1:

SELECT t.*,
       coalesce(MAX(CASE WHEN ct_vl = 1 THEN dt END) OVER(PARTITION BY ID),FIRST_VALUE(dt) OVER(PARTITION BY ID ORDER BY dt)) final_dt
FROM T t

Results:

| ID | CD |       DT | CT_VL | FINAL_DT |
|----|----|----------|-------|----------|
|  1 |  A | 20210101 |     0 | 20210101 |
|  1 |  B | 20210201 |     0 | 20210101 |
|  1 |  A | 20210301 |     0 | 20210101 |
|  2 |  A | 20210101 |     0 | 20210201 |
|  2 |  B | 20210201 |     1 | 20210201 |
|  3 |  C | 20210101 |     0 | 20210301 |
|  3 |  V | 20210201 |     1 | 20210301 |
|  3 |  C | 20210301 |     1 | 20210301 |
|  3 |  C | 20210401 |     0 | 20210301 |

CodePudding user response：

Try following:

Query 1:

SELECT 
    a.*, b.dt AS final_dt 
FROM myTable a 
INNER JOIN (
    SELECT cd, MIN(dt) dt FROM myTable WHERE ct_val = 0 GROUP BY cd
    UNION ALL
    SELECT cd, MAX(dt) dt FROM myTable WHERE ct_val = 1 GROUP BY cd
) b ON a.cd = b.cd

Query 2:

SELECT 
    a.*, (CASE WHEN a.ct_val = 0 THEN minDt ELSE maxDt END) AS final_dt 
FROM myTable a 
INNER JOIN (
    SELECT 
        cd, MIN(dt) minDt, MAX(dt) maxDt 
    FROM myTable 
    GROUP BY cd
) b ON a.cd = b.cd

I think your desire output would be return by Query 2