So, we have a table with companies and what their fiscal year month end is. For example, for Apple, it is September (9), for Dell it is January (1), for Union Pacific is it December (12), for Axos it is June (6).

Then there is another table with some data entry, and a date which represents the date the data reflects. Basically it is a calendar date like 12/7/2021, or 9/6/2020, or 3/7/2022.

I need to come up with some algorithm where I can take that date, and based on the fiscal year month end, derive a value for the date in this manner: 4Q22 (4th quarter of fiscal year 2022), FY23 (fiscal year 2023), 3Q21 (3rd quarter fiscal year 2021.

So in the case of say Axos, if the date on the data is 6/3/2022, I can get 2 values: 4Q22 & FY22. But, for Apple, if the date on the data is 6/2/2022, then the dates would be: 3Q22 & FY22.

If anyone can help with some logic for this, it would help a lot.

Thanks!

CodePudding user response：

You could adjust the date by the number of months for the fiscal year end, and then just convert to strings:

  to_char(add_months(trunc(data_date, 'MM'), 12 - fy_end_month), '"FY"YY') as fy
  to_char(add_months(trunc(data_date, 'MM'), 12 - fy_end_month), 'Q"Q"YY') as fq

With your examples that would give:

COMPANY	FY_END_MONTH	DATA_DATE	FY	FQ
Apple	9	02-JUN-22	FY22	3Q22
Axos	6	03-JUN-22	FY22	4Q22

db<>fiddle cross-joining all the dates with all the companies so you can see all the combinations, not just the two above (and the intermediate values); and also showing the result for all months in this year for all four companies.

CodePudding user response：

If you have the sample data:

CREATE TABLE fiscal_years(company, fy_end_month) AS
SELECT 'Apple',          9 FROM DUAL UNION ALL
SELECT 'Dell',           1 FROM DUAL UNION ALL
SELECT 'Union Pacific', 12 FROM DUAL UNION ALL
SELECT 'Axos',           6 FROM DUAL;

CREATE TABLE calendar_dates (dt) AS
SELECT DATE '2021-07-12' FROM DUAL UNION ALL
SELECT DATE '2020-06-09' FROM DUAL UNION ALL
SELECT DATE '2022-07-03' FROM DUAL;

Then you can subtract months equal than the month of the company's fiscal year end (unless it is 12 when you subtract nothing) and then use TO_CHAR to get your required format:

SELECT f.company,
       c.dt,
       TO_CHAR( ADD_MONTHS(c.dt, -MOD(fy_end_month, 12)), '"FY"YYYY')
         || TO_CHAR( ADD_MONTHS(c.dt, 12 - fy_end_month), '"-"YYYY')
         AS fy,
       TO_CHAR( ADD_MONTHS(c.dt, -MOD(fy_end_month, 12)), 'Q"Q"YY') AS quarter
FROM   calendar_dates c
       CROSS JOIN fiscal_years f
ORDER BY company, dt

Note: if you had details of which company the date applied to then you could use an INNER JOIN but the example above just applies all the dates to all companies using a CROSS JOIN.

Which outputs:

COMPANY	DT	FY	QUARTER
Apple	09-JUN-20	FY2019-2020	3Q19
Apple	12-JUL-21	FY2020-2021	4Q20
Apple	03-JUL-22	FY2021-2022	4Q21
Axos	09-JUN-20	FY2019-2020	4Q19
Axos	12-JUL-21	FY2021-2022	1Q21
Axos	03-JUL-22	FY2022-2023	1Q22
Dell	09-JUN-20	FY2020-2021	2Q20
Dell	12-JUL-21	FY2021-2022	2Q21
Dell	03-JUL-22	FY2022-2023	2Q22
Union Pacific	09-JUN-20	FY2020-2020	2Q20
Union Pacific	12-JUL-21	FY2021-2021	3Q21
Union Pacific	03-JUL-22	FY2022-2022	3Q22

db<>fiddle here