How to create date table

building semantic layer
1

Hi, I’ve been searching the BOB but I haven’t found a satisfying answer yet, so here goes:
Is there a way to create a (derived?) table in a universe which contains all dates between a given start date and a given end date?

The story is I need to create a report containing number of days in a month where specific data is linked to. We can only use working days/ business days, so no weekend days.
F.E. I have a table with first and last dates of the months, but none of the dates between. There are other tables which have some of the dates I need…

I use the latest BOXI, DB is Oracle 10

Skradush (BOB member since 2010-01-13)

2

You’re better with a physical calendar table than a derived table.

There are scripts out there (try googling) that do such a thing.

3

Can i create a physical calendar within the universe or did you mean within the database?
Cause I have no access to the latter

Skradush (BOB member since 2010-01-13)

4

Put a request in to your dba to get a database table created.

5

Try something like this for the derived table, the Day of the week only brings back Mon- Fri.

SELECT
DTE.DAY_OF_WEEK,
DTE.CALENDAR_DATE
FROM
(
SELECT calendar_date,
TO_CHAR (calendar_date, ‘D’) as day_of_week
FROM DATE_DIM.Date
where to_char(DATE_DIM.CALENDAR_DATE, ‘D’)<> ALL(‘1’,‘7’)
) DTE

DK MC (BOB member since 2010-02-18)

6

Where is he going to get DATE_DIM from? :crazy_face:

7

Hi Skradush,

Check this old topic if you use an Oracle database:

Marek Chladny :slovakia: (BOB member since 2003-11-27)