Calendar Table for Oil & Gas Data (Date Dimension)

Dates in the Oil & Gas industry data can be a little tricky.

Especially when aggregating data, such as production volumes, to a daily level, and trying to connect multiple tables with date columns.

Some tables, such as tank levels or production volumes, could be based on a Gauge Date (aka Report Date) since the data is measured, aggregated, reported or “gauged” on that date (today), but in essence it is yesterday’s data, or to be more accurate, it is the data for the 24 hour period that began yesterday at the contract hour (e.g. 6am) and ended today at that same time, or the Production Date.

Other tables could have actual dates, such as sales tickets, run tickets, operation logs, etc.

The Problem with misaligned date columns

Consider the following scenario when one table contains Gauge Dates and another table contains Production Dates, and you need to combine them via a join.

Table 1: The data you want for March is from March 2nd to April 1st

Table 2: The data you want for March is from March 1st to March 31st.

If you join these tables by creating a relationship between their date columns your dataset will be misaligned, sometimes leading to obvious issues like negative production.

You can solve this in many ways, such as creating a calculated column or using date transforms in your SQL join, but that can become messy, especially if you are dealing with many tables. So here’s an easier way.

Dates Table To The Rescue

When joining these tables becomes a problem, you can use the calendar table below to solve the problem.

You can use the SQL script below to create an Oil & Gas Date Table that fixes this problem.

if object_id('#_calendar') is not null
    drop table [#_calendar]

create table #_calendar
(
    gauge_date date primary key,
    gauge_year smallint,
    gauge_month tinyint,
    gauge_month_group date,
    gauge_yyyymm char(6),

    /* production is a day behind */
    production_date date,
    production_year smallint,
    production_month tinyint,
    production_month_group date,
    production_yyyymm char(6)
)

-- first date in the _calendar table
declare @start_dt as date = '1/1/2023'; 

-- last date in the _calendar table 
declare @end_dt as date = '12/31/2023'; 

while @start_dt <= @end_dt
begin
    insert into #_calendar
    (
        gauge_date,
        gauge_year,
        gauge_month,
        gauge_month_group,
        gauge_yyyymm,
        production_date,
        production_year,
        production_month,
        production_month_group,
        production_yyyymm
    )
    values
    (@start_dt,
     year(@start_dt),
     month(@start_dt),
     dateadd(day, - (day(@start_dt) - 1),
               @start_dt),
     concat(year(@start_dt), 
       right('0' + rtrim(month(@start_dt)), 2)),
     dateadd(day, -1, @start_dt),
     year(dateadd(day, -1, @start_dt)),
     month(dateadd(day, -1, @start_dt)),
     dateadd(day, -(day(dateadd(
               day, -1, @start_dt)) - 1), 
       dateadd(day, -1, @start_dt)),
     concat(year(dateadd(day, -1, @start_dt)), 
       right('0' + rtrim(month(dateadd(
               day, -1, @start_dt))), 2))
    )
    
set @start_dt = dateadd(day, 1, @start_dt)
end

select *
from #_calendar
order by gauge_date

drop table #_calendar

When you run this script, the results look like this:

Now when you are creating relationships between tables, you can connect their date columns to the proper column in the date table instead, and your data will be better aligned!

If a data table uses a gauge date column, link it to the gauge_date column. And if a table uses an actual or production date column, link it to the production_date column. You can then use the column you want to display on your report from the calendar table, such as production_date to get the correct data.

Don’t forget to change the values of @start_dt and @end_dt to match your needs.