Moving and Replicating Data into a new column in query-CodePudding

I have an issue where I’m trying to get the output from one of the columns duplicated into a new column with the same value.

I have this query:


SELECT x.*,
       CASE   
            WHEN x.depositMonth = x.firstActivityMonth THEN 'Y' 
                     ELSE 'N'
       END Converted,

       CASE
           WHEN x.depositMonth = x.firstActivityBetMonth THEN x.depositMonth
                     ELSE '-'
       END convertedMonth
 
       FROM 
            customerStats x

The results for that query are:

  ID    depositMonth firstActivity  realGame  deposits  converted  convertedMonth
 a-123   2022-09      2022-03       N            5         N         -
 a-123   2022-10      2022-03       N            6         N         -
 a-123   2022-03      2022-03       Y            5         Y         2022-03
 a-124   2021-09      2019-08       N            2         N         -
 a-124   2021-10      2019-08       N            3         N         -
 a-124   2022-03      2019-08       Y            1         Y         2022-03

For the last column I need that same date for the converted month to be replicated like this across each row.

Desired output:

   ID   depositMonth firstActivity  realGame  deposits  converted  convertedMonth
 a-123   2022-09      2022-03       N            5         N         2022-03
 a-123   2022-10      2022-03       N            6         N         2022-03
 a-123   2022-03      2022-03       Y            5         Y         2022-03
 a-124   2021-09      2019-08       N            2         N         2022-03
 a-124   2021-10      2019-08       N            3         N         2022-03
 a-124   2022-03      2019-08       Y            1         Y         2022-03

This is just a single example, i have a lot of customers I need to apply this for in the data.

Kind Regards

CodePudding user response：

You seem like want to use MAX condition window function which can put conditions in MAX aggregate function to judge the latest row of logic.

SELECT x.*,
       CASE   
            WHEN x.depositMonth = x.firstActivity THEN 'Y' 
                     ELSE 'N'
       END Converted,
       MAX(CASE WHEN x.depositMonth = x.firstActivity THEN x.depositMonth
                     ELSE '-'
       END) OVER(PARTITION BY ID) convertedMonth
FROM 
    customerStats x

from your sample data and expected results, you can try to put realGame = 'Y' to determine lastest row each ID

SELECT x.*,
       CASE   
            WHEN x.depositMonth = x.firstActivity THEN 'Y' 
                     ELSE 'N'
       END Converted,
       MAX(CASE WHEN realGame = 'Y' THEN x.depositMonth ELSE '-'  END) OVER(PARTITION BY ID) convertedMonth
FROM 
    customerStats x

sqlfiddle