sql - How to create a 4-4-5 fiscal calendar using python?

Question

I would like to have a dataframe consisting of the corresponding week numbers and month every date from 1/1/21 to 31/12/50 would belong to. For instance,

  date  |   day   | week_no | yyyy-mm | quarter
01/01/21| Friday  |    40   | 2021-01 | Q4 2021
02/01/21| Saturday|    40   | 2021-01 | Q4 2021
.
.
24/01/21| Sunday  |    44   | 2021-02 | Q4 2021
.
.
01/04/21| Thursday|    53   | 2021-03 | Q4 2021
.
04/04/21| Sunday  |    1    | 2021-04 | Q1 2022

I found a wrapper class in Pandas called FY5253Quarter and fiscal445 in python, however, I am not sure how I can use this class to achieve what I require. Thanks in advance for all the help :)

Answer 1

Creating calendar table could be done with SQL. Here example using PostgreSQL:

WITH RECURSIVE cte AS (
   SELECT CAST('2021-01-01' AS date) d
   UNION ALL
   SELECT d+1
   FROM cte
   WHERE d < CAST('2030-12-31' AS DATE)
)
SELECT 
  d AS "date",
  to_char(d, 'DAY') AS day,
  to_char(d, 'iw') AS week_no,
  to_char(d, 'yyyy-mm') AS "yyyy-mm",
  to_char(d, '"Q"q yyyy') AS "quarter"
FROM cte;

db<>fiddle demo

How it works:

1. Recursive part generate date rows from 2021-01-01 to defined end date
2. Main query generate columns with proper string formatting

Now by having the query you could materialize it with CREATE TABLE myCalendar AS ... if needed.

If you are using different RDBMS, the pattern is basically the same - the only difference is using dialect specific functions.

For instance PostgreSQL has its own function generate_series:

SELECT * /* format functions here */
FROM generate_series(date '2021-01-01', date '2030-12-31', '1 day');

EDIT:

How can I specify that I want a 4-4-5 calendar because from what I understand it seems you're creating a normal calendar?

Yes, here it is a normal calendar. The logic could be rewritten, but you already found a working solution.

Also I found a link to PostgreSQL for fiscal 4-4-5 github.com/sqlsunday/calendar/blob/boss/CalendarFunctions.sql but I always meet with an error running the script for function Calendar.Fiscal_4_4_5

The point is it's written for SQL Server in TSQL as table-valued function. To call it use:

SELECT *
FROM  Calendar.Fiscal_4_4_5 ('2021-01-01', '2030-12-31', '2021-01-01','445',1);

db<>fiddle demo