How to calculate Week Start Date and Week End date using DAY_DT in Obiee 11g

In OBIEE 11g, many times we have to calculate on the fly, the various dates functions like Week_Start_date, Week_End_Date, Previous_Yr_Date etc,  just using the Date column. Few projects have Date Dimensions not properly structured to have all columns. So here we have the logical sql’s to calculate the same:
We can calculate the week start date using the below sql:
TIMESTAMPADD(SQL_TSI_DAY,- DAYOFWEEK(MIN(“Dim – Date”.” Date Column”)) + 1, MIN(“Dim – Date”.” Date Column”))
For Week End Date, use the below Sql:
TIMESTAMPADD(SQL_TSI_DAY, 7-DAYOFWEEK(MAX(“Dim – Date”.” Date Column”)), MAX(“Dim – Date”.”Date Column”))
DAYOFWEEK returns an integer in the range of 1 to 7. A value of 1 represents the Monday.
Courtesy: Google 
🙂
Advertisements

One thought on “How to calculate Week Start Date and Week End date using DAY_DT in Obiee 11g”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s