0

We have an application which basically takes a companies fiscal year end, and walks backwards to gather ways using CONNECT BY LEVEL and looks up other data.

However, we found some companies that change their fiscal cycle causing the CONNECT BY LEVEL not to work properly. See the code, hopefully someone can give some creative idea?

First, see here how the company has December as their FY ending date, but then in 2018 changed to November:

11/30/2020
11/30/2019
11/30/2018
12/31/2017
12/31/2016
12/31/2015

The query developed looks like this:

  SELECT TO_CHAR(a.fy,'MM/DD/YYYY') AS fy, DECODE(b.net_sales,NULL,'NA',b.net_sales) AS net_sales
  FROM (SELECT ADD_MONTHS(LAST_DAY(TO_DATE(TO_CHAR(period_date), 'YYYYMM')), (LEVEL - 1) * -12) AS fy
        FROM (SELECT m_ticker, period_type, MAX(period_date) AS period_date
              FROM period_data
              WHERE ticker = 'LUK'
                AND period_type = 'A'
                AND adjusted_revenue IS NOT NULL
              GROUP BY ticker, period_type)
        CONNECT BY LEVEL <= 5
        ORDER BY 1 ASC) a,
       (SELECT LAST_DAY(TO_DATE(TO_CHAR(period_date),'YYYYMM')) AS fy, TO_CHAR(NVL(adjusted_revenue,0)) AS net_sales
        FROM period_data
        WHERE ticker = 'LUK'
          AND period_type = 'A'
        ORDER BY period_date DESC NULLS LAST) b
  WHERE a.fy = b.fy(+) ORDER BY 1 DESC;

As such, I get this output:

11/30/2018  5009.73
11/30/2017  NA
11/30/2016  NA
11/30/2015  NA
11/30/2014  NA

Because 2018 is 11/2018, but starting in 2017, it is 12/2017. The month changed, so subtracting 12 will not work anymore.......

Hoping to return values regardless of the value for the FY end......

Landon Statis
  • 683
  • 2
  • 10
  • 25
  • 1
    . . I don't think there is a way to programmatically know that a company changed its fiscal year. You need some sort of calendar table for that. – Gordon Linoff Jun 15 '19 at 01:48
  • Can you UNION separate time based queries based on when fiscal calendar changed? – alexherm Jun 15 '19 at 02:15
  • Could you provide sample date for all tables used in query? Is there a typo in mostinner query? Should there be `ticker` column instead of `m_ticker`? Otherwise it's not correct group by expression. If I understood you well, you would like to find out what is `net_sales` for last five years for given `ticker` and `period_type`. In my opinion there is no need for using connect by. You could just select last FY and filter `period_data` for last 5 years of data. – robertus Jun 18 '19 at 20:32
  • Another question: Does gaps in FY's are possible? Could happen there is FY 2017, then 2018, but NO 2019, and after that it is 2020? – robertus Jun 18 '19 at 20:42

0 Answers0