Table in Oracle which will store the Server count on monthly basis

Question

I have to create a table in Oracle in similar that can be seen below. As you can see months and year is combined and then storing the monthly server count data based on the description type. To fetch the data, I am using Oracle. Month & Year column keeps continuing. Also need to schedule this job for each month so the MonthYear column gets automatically added in table, and the value gets stored periodically. Any tips are welcome.

Answer 1

In SQL (not just Oracle), a table has a fixed number of columns. When you consider a solution that adds a new column every month then you are trying to do something in a non-SQL way.

Don't have columns for each month. Transpose your table so that month is a single column and when you want to add a new month's data then you insert a new row. Which is much simpler than adding more columns.

Something like:

CREATE TABLE table_name (
  month DATE
        CONSTRAINT table_name__month__pk PRIMARY KEY
        CONSTRAINT table_name__month__chk CHECK (month = TRUNC(month, 'MM')),
  server_count_start NUMBER(10,0) NOT NULL,
  new_servers        NUMBER(10,0) DEFAULT 0 NOT NULL,
  m_and_a_addition   NUMBER(10,0) DEFAULT 0 NOT NULL,
  moved_to_cloud     NUMBER(10,0) DEFAULT 0 NOT NULL,
  decomissioned      NUMBER(10,0) DEFAULT 0 NOT NULL
);

Note: do not store the end-of-month total as you can calculate it using server_start_count + new_servers + m_and_a_addition - moved_to_cloud - decomissioned. If you were using Oracle 12 then you could add a virtual column and use GENERATED ALWAYS AS (server_start_count + new_servers + m_and_a_addition - moved_to_cloud - decomissioned) but you are using a very old version of Oracle and that is not supported (please upgrade).

If you then want to display the output using months as columns then you can PIVOT the table, either in SQL or in whatever third-party application you are using to access the data. But for storing the data, add rows, not columns, which requires a simple INSERT statement.

Answer 2

I have upgraded Oracle and tried running the query, but it's throwing an error as below:

Error report - ORA-02436: date or system variable wrongly specified in CHECK constraint 02436. 00000 - "date or system variable wrongly specified in CHECK constraint"

CREATE TABLE REP_Servers_by_BU(
  month DATE
        CONSTRAINT REP_Servers_by_BU__month__pk PRIMARY KEY
        CONSTRAINT REP_Servers_by_BU__month__chk CHECK (month =  TRUNC(TRUNC(LAST_DAY(ADD_MONTHS(CURRENT_DATE, -2))) + 1,'MM')),
        
  server_count_start      NUMBER(10,0) NOT NULL,
  new_servers_provision   NUMBER(10,0) DEFAULT 0 NOT NULL,
  m_and_a_server_addition NUMBER(10,0) DEFAULT 0 NOT NULL,
  moved_to_cloud     NUMBER(10,0) DEFAULT 0 NOT NULL,
  decomissioned      NUMBER(10,0) DEFAULT 0 NOT NULL,
  server_count_last NUMBER(10,0) generated always AS ( server_count_start  + new_servers_provision + m_and_a_server_addition - moved_to_cloud - decomissioned) VIRTUAL
)

I want to add 2 columns in a table which will show previous month start date, month, and year combined as below:

	FIRST_DATE	PREVIOUS_MONTH_YEAR
1	01-10-2023	OCT-23