Hi Guys, Need suggestions for the below.
Need to identify if the performance is Red for the past 3 months (excluding current month) and mark the flag as Y in the current month record. Each record has month end date.
Output table will have only current month data. Previous months data are just for comparison and and it must be filtered out once done.
Data:
| REPORTING_PERIOD | ID | Performance |
| 30/11/2017 | 100 | Green |
| 31/12/2017 | 100 | Green |
| 31/01/2018 | 100 | Red |
| 28/02/2018 | 100 | Red |
| 31/03/2018 | 100 | Red |
| 31/12/2017 | 200 | Green |
| 31/01/2018 | 200 | Red |
| 28/02/2018 | 200 | Red |
| 31/03/2018 | 200 | Red |
Output:
| REPORTING_PERIOD | ID | Performance | Past3Months Red Flag |
| 31/03/2018 | 100 | Red | Y |
| 31/03/2018 | 200 | Red | N |
Thanks:
