I have a table that is similar to the one below:
| Name | Current submission | Submission | Date |
|---|---|---|---|
| A | 43-01 | 43-01 | 1-1-2019 |
| A | 43-01 | 42-01 | 1-1-2020 |
| A | 43-01 | 42-02 | 1-1-2021 |
| A | 43-01 | 42-03 | 1-1-2022 |
I want it to look like the following:
| Name | Current submission | Submission | Date |
|---|---|---|---|
| A | 42-03 | 43-01 | 1-1-2019 |
| A | 42-03 | 42-01 | 1-1-2020 |
| A | 42-03 | 42-02 | 1-1-2021 |
| A | 42-03 | 42-03 | 1-1-2022 |
Here is the code I used to get the first table:
SELECT
Submission,
MAX(Submission) over (partition by Name) as [Current Submission],
Date
FROM Table_Name
I have tried to do a CASE WHEN statement using MAX(Date) but I can’t get the logic right…
>Solution :
I was literally writing this when the last comment was made.
select name,
first_value(submission) over (partition by name order by date desc) as current_submission,
submission,
date
from my_table