I have a table with the following fields:
| TimeTable |
|---|
| Period |
| PeriodID |
| Current |
Period: Last 12 Months, Last 6months, YTD, QTD
PeriodID: YYMM
Current: Y/N (for example if the months are part of the last 12 months then Y otherwise the remaining 12 will be N).
The table were created using a mastercalendar table, where there a rownumber and the row 1 is associated to the last date
I was asked now to create an additional field, Associated Period
For example, for the Last 12 Months would look like this:
| PeriodID | Period | Current | Associated |
|---|
| 1712 | Last12Months | Y | 1712 |
| 1612 | Last12Months | N | 1712 |
| 1711 | Last12Months | Y | 1711 |
| 1611 | Last12Months | N | 1711 |
I manage to create the values for the last 12 months, by only adding +100 when the rows less than 12. However I am finding hard to find the solution for the remaining periods.
Does anybody have any tips or done something similar before?
Hope I was clear enough...
Thank you!
