Date Range Table

[system]

Hello folks,

Years back I used a table in conjunction with the standard DIM_DATE table.

Something like a DATE_RANGE table…

it was a table that was joined to the DIM_DATE table to get ranges

and had (forgot if it was columns or rows…must have been rows…)

values like

Current month
Current quarter
Current year
Last month
Last quarter
Last year
Today
Yesterday

Now I do not recollect the structure of the table or how it was derived…

It would re-populate every day I think (maybe for rows like today and yesterday) …and maybe atleast every weekend or monthend for other values)

It would help end users select the text values and accordingly populate dates from the DIM_DATE and FACT table.

Anyone any ideas of what I am saying…I am assuming its a common table used across the data warehouse world.

Or feel free to let me know if the experts have better ideas and what could be done please !!

thanks for your help !

(re-editing the question to add more info that I just remembered …if my brain remebers it right…)

I think it had columns like

Start_Dt…End_Dt…Text
=====…=====…===
4/16/15…4/16/15…Today
4/15/15…4/15/15…Yesterday
4/1/15…4/30/15…This Month
3/1/15…3/31/15…Last Month

and then the join from DIM_DATE table would be as follows

DIM_DATE.DATE_KEY between DATE_RANGE.START_DT and DATE_RANGE.END_DT

Any standards stored procedures already exist out there to populate this table ?

Or maybe something standard in Informatica?

Thanks Again !!

---

iinquisitivee (BOB member since 2015-03-07)

[MarkP]

It’s not something that I’ve used as I tend to build objects into the universe so that anyone writing reports can use them to compare to whatever date object they want to do.

This topic will help with that:

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2

As per this topic on Oracle relative dates, here’s the same set of dates in SQL Server. Please note that some may not work on SQL Server 2000, but all definitely work in 2005/8. Yesterday cast(convert(char(10),dateadd(d,-1,getdate()),23) as datetime) Today cast(convert(char(10),getdate(),23) as datetime) The following also works for today: dateadd(dd, datediff(dd,0,getdate()), 0) Start of Current Month cast(convert(char(7),getdate(),23)+'-01' as datetime) End of Current Month dateadd(d,-1…

It’s an alternative approach to what you’ve discussed and is easy to implement.

Regards,
Mark

[system]

Thanks Mark !

You mean, create seperate filters at the universe level, right ?

I checked the forumlas and am thinking, using the same folumulas that you mentioned just create a table with somewhere around 10 to 20 rows.
One for each relation - today, yesterday, this month, last month, etc…

The difference would be that the user can use the same object fiter and keep changing dates on the fly if they like…(without having to change the filter object)

Also, if I had multiple tools in the orgnization…say Business Objects and Tableau, they can both use the same underlying table.

Thanks,

---

iinquisitivee (BOB member since 2015-03-07)