I have a table with periods and dates. The columns are fixed but sometimes period 13 can be null . I need the master calendar to incorporate fiscal year and period (1-13) based on this data. I am not sure of the best way to approach this.
As a temporary measure I created an excel with a list dates and the associated period but ideally I would like it to be dynamic and based off this table but I'm not sure how to adapt the master calendar. I searched other posts but they seem to be based on a fixed rule rather than a table of start and end dates.
Any pointers or tips would be much appreciated.
Thanks
| fiscal_year | per_start##1 | per_end##1 | per_start##2 | per_end##2 | per_start##3 | per_end##3 | per_start##4 | per_end##4 | per_start##5 | per_end##5 | per_start##6 | per_end##6 | per_start##7 | per_end##7 | per_start##8 | per_end##8 | per_start##9 | per_end##9 | per_start##10 | per_end##10 | per_start##11 | per_end##11 | per_start##12 | per_end##12 | per_start##13 | per_end##13 |
| 2017 | 02/10/2016 | 29/10/2016 | 30/10/2016 | 03/12/2016 | 04/12/2016 | 31/12/2016 | 01/01/2017 | 28/01/2017 | 29/01/2017 | 25/02/2017 | 26/02/2017 | 01/04/2017 | 02/04/2017 | 29/04/2017 | 30/04/2017 | 03/06/2017 | 04/06/2017 | 01/07/2017 | 02/07/2017 | 29/07/2017 | 30/07/2017 | 02/09/2017 | 03/09/2017 | 30/09/2017 | NULL | NULL |
| 2018 | 01/10/2017 | 28/10/2017 | 29/10/2017 | 02/12/2017 | 03/12/2017 | 30/12/2017 | 31/12/2017 | 03/02/2018 | 04/02/2018 | 03/03/2018 | 04/03/2018 | 31/03/2018 | 01/04/2018 | 28/04/2018 | 29/04/2018 | 02/06/2018 | 03/06/2018 | 30/06/2018 | 01/07/2018 | 28/07/2018 | 29/07/2018 | 01/09/2018 | 02/09/2018 | 29/09/2018 | NULL | NULL |
| 2019 | 30/09/2018 | 03/11/2018 | 04/11/2018 | 01/12/2018 | 02/12/2018 | 29/12/2018 | 30/12/2018 | 02/02/2019 | 03/02/2019 | 02/03/2019 | 03/03/2019 | 30/03/2019 | 31/03/2019 | 27/04/2019 | 28/04/2019 | 01/06/2019 | 02/06/2019 | 29/06/2019 | 30/06/2019 | 03/08/2019 | 04/08/2019 | 24/08/2019 | 25/08/2019 | 28/09/2019 | 29/09/2019 | 29/09/2019 |
| 2020 | 30/09/2019 | 02/11/2019 | 03/11/2019 | 30/11/2019 | 01/12/2019 | 28/12/2019 | 29/12/2019 | 01/02/2020 | 02/02/2020 | 29/02/2020 | 01/03/2020 | 28/03/2020 | 29/03/2020 | 02/05/2020 | 03/05/2020 | 30/05/2020 | 31/05/2020 | 27/06/2020 | 28/06/2020 | 01/08/2020 | 02/08/2020 | 29/08/2020 | 30/08/2020 | 03/10/2020 | NULL | NULL |
| 2021 | 04/10/2020 | 06/11/2020 | 07/11/2020 | 05/12/2020 | 06/12/2020 | 02/01/2021 | 03/01/2021 | 06/02/2021 | 07/02/2021 | 06/03/2021 | 07/03/2021 | 03/04/2021 | 04/04/2021 | 08/05/2021 | 09/05/2021 | 05/06/2021 | 06/06/2021 | 03/07/2021 | 04/07/2021 | 07/08/2021 | 08/08/2021 | 04/09/2021 | 05/09/2021 | 02/10/2021 | NULL | NULL |
CalendarMaster:
LOAD
Date(InvoiceDate) AS InvoiceDate,
Year(InvoiceDate) AS Year,
'Q' & Ceil(Month(InvoiceDate) / 3) AS Quarter,
Month(InvoiceDate) As Month,
Day(InvoiceDate) As Day,
Week(InvoiceDate) As Week;
Load Date(MinDate + IterNo() -1 ) AS InvoiceDate While (MinDate + IterNo() - 1) <= Num(MaxDate);
Load
Min(InvoiceDate) AS MinDate,
Max(InvoiceDate) AS MaxDate
RESIDENT Invoice;
