Query to find day number of the fiscal month in oracle SQL

Question

The query should return the day number of the current fiscal month.Check the below query:

SELECT *
FROM GL_PERIODS
WHERE PERIOD_SET_NAME='Fiscal Year'
AND period_year =(SELECT DISTINCT period_year
                                FROM gl_periods
                               WHERE     TRUNC (SYSDATE) BETWEEN start_date
                                                             AND end_date)

For period name : SEP-19, Start_Date is 8/26/2018. So, day number should be 16 for sysdate.

Sample Data:

period_set_name    period_name    start_date    end_date   entered_period_name
Fiscal Year         AUG-19        7/29/2018    8/25/2018    AUG
Fiscal Year         SEP-19        8/26/2018    9/22/2018    SEP
Fiscal Year         OCT-19        9/23/2018    10/27/2018   OCT

Expected Output: 16

Your sample data does not have a `period_year` column and the `period_name` matches the month in `entered_period_name` and `end_date` but does not match the year in `start_date` and `end_date`. — MT0, Sep 14 '18 at 09:32
How do you get the expected output of `16`? Today's date is `2018-09-14` which is the 20th day of your `SEP` period (26th - 30th is 6 days of August and 1st to 14th is 14 days of September). — MT0, Sep 14 '18 at 09:49

score 0 · Answer 1 · answered Sep 14 '18 at 09:27

We can do arithmetic on dates. So today's Fiscal day number would be:

SELECT (trunc(sysdate) - start_date) + 1 as fiscal_day_no
FROM GL_PERIODS
WHERE PERIOD_SET_NAME='Fiscal Year'
AND period_year =(SELECT DISTINCT period_year
                            FROM gl_periods
                           WHERE     TRUNC (SYSDATE) BETWEEN start_date
                                                         AND end_date)

This assumes you have no special requirement to handle e.g. weekends or public holidays.

score 0 · Answer 2 · answered Sep 14 '18 at 09:46

Oracle Setup:

CREATE TABLE GL_PERIODS(
  period_set_name VARCHAR2(20),
  start_date          DATE,
  end_date            DATE,
  period_name         CHAR(6) GENERATED ALWAYS AS ( CAST( TO_CHAR( end_date, 'MON-YY' ) AS CHAR(6) ) ) VIRTUAL,
  entered_period_name CHAR(3) GENERATED ALWAYS AS ( CAST( TO_CHAR( end_date, 'MON' ) AS CHAR(3) ) ) VIRTUAL,
  period_year         NUMBER(4) GENERATED ALWAYS AS ( EXTRACT( YEAR FROM end_date ) ) VIRTUAL
);

INSERT INTO GL_PERIODS( period_set_name, start_date, end_date )
  SELECT 'Fiscal Year', DATE '2018-07-29', DATE '2018-08-25' FROM DUAL UNION ALL
  SELECT 'Fiscal Year', DATE '2018-08-26', DATE '2018-09-22' FROM DUAL UNION ALL
  SELECT 'Fiscal Year', DATE '2018-09-23', DATE '2018-10-27' FROM DUAL;

Query:

SELECT TRUNC( SYSDATE - start_date + 1 ) AS fiscal_day_no
FROM   GL_PERIODS
WHERE  start_date <= SYSDATE
AND    SYSDATE < END_DATE + INTERVAL '1' DAY;

Output:

| FISCAL_DAY_NO |
|---------------|
|            20 |

Query to find day number of the fiscal month in oracle SQL

2 Answers2