Aug 30 2007
Dynamic Dates Part II: Weekly Date Ranges
Last Time On This Subject…
In the first post in this series I set up both regular objects and predefined condition objects that were based on the Oracle sysdate pseudo-column. These objects were designed to be used for scheduling reports with date conditions while allowing the date range to move forward in time for each new execution of the scheduled report. For that article I only created objects for Today and Yesterday. Today π I will provide some weekly date ranges with the help of a few Oracle functions.
Yesterday Once More
Here is a review of what I ended up with for my daily objects in the prior post.
| Date | Formula |
|---|---|
| Today | trunc(sysdate) |
| Yesterday | trunc(sysdate-1) |
Where can we go from here? It turns out that time travel is possible, given the right functions. π
Weekly Dates
The first thing I need to decide is when my week starts. I will use a calendar where the week starts on Sunday and ends on Saturday. The function that I will use to derive those dates is called next_day(). The syntax for this function is:
NEXT_DAY(<date>, <day of the week>)
What this will do is take the input date and return the date of the “next” day based on the second function parameter. Let’s suggest that today is Tuesday. The next Saturday ( the end of the week) would be four days away. You could write a fancy case statement to determine that, but the next_day() function takes all the effort out of it. Simply write this:
next_day(trunc(sysdate), 'Saturday')
And the next thing you know, you have the date for the end of the current week. Or do you?
Testing the Boundaries
With any condition that involves a range it is important to check the boundaries. For example, what is the return value for the function outlined above if today is already Saturday? Unfortunately, it isn’t the correct Saturday. Instead, it will be the Saturday that ends the following week. That means that we have a formula that works for 6 out of the seven days of the week. How can I know that it is Saturday? I need a new function.
For this I will use the to_char() function. I don’t care what the date is, but I do need to know what the day is. The formula for that is:
to_char(sysdate,'Day')
If today is a Saturday, then the return value for this function is the day name Saturday. There are different argument options that can be used here… for example using ‘Dy’ instead of ‘Day’ would return Sat instead of Saturday. Either works for this purpose.
Now that I have a way to know if it is Saturday or not, I can handle my boundary condition. There are a couple of things that I can do. I have opted to check to see if it is Saturday, and if it is, back up one day before applying the next_day() function. Backing up one day changes sysdate to a Friday, and then the “next” Saturday would be the required date instead of a week later. Here’s the completed formula:
next_day(case to_char(sysdate,'Day') when 'Saturday' then trunc(sysdate-1) else trunc(sysdate) end, 'Saturday')
That code can be used to create an object called End of Current Week. This object can then be used in conditions as I outlined in the first post in this series.
Start of Current Week
To get to the start of the week I might like to use a previous_day() function. However Oracle does not provide that. Not to worry, there is a very simple solution. All I need to do is back up 7 days and then apply the next_day function to get the Sunday date that starts the current week.
next_day(trunc(sysdate-7), 'Sunday')
Since I had an issue with Saturday boundary condition I want to also check what happens on Sunday using the above formula. It turns out that we don’t have a problem. If today is any day other than Sunday, and I back up 7 days, then the next Sunday is the proper date. What if my current date is actually Sunday? After I back up 7 days, the “next” Sunday just brings me right back to where I started from. So while it might seem like extra work to go back a week just to come back to the same date value, the simple formula as posted above works just fine on the Sunday boundary condition.
Here is some sample data that shows what the output from these formulas will look like over a few weeks of data:
| Date | Day Name | Week Start Date | Week End Date |
|---|---|---|---|
| 22-Aug-07 | WED | 19-Aug-07 | 25-Aug-07 |
| 23-Aug-07 | THU | 19-Aug-07 | 25-Aug-07 |
| 24-Aug-07 | FRI | 19-Aug-07 | 25-Aug-07 |
| 25-Aug-07 | SAT | 19-Aug-07 | 25-Aug-07 |
| 26-Aug-07 | SUN | 26-Aug-07 | 1-Sep-07 |
| 27-Aug-07 | MON | 26-Aug-07 | 1-Sep-07 |
| 28-Aug-07 | TUE | 26-Aug-07 | 1-Sep-07 |
| 29-Aug-07 | WED | 26-Aug-07 | 1-Sep-07 |
| 30-Aug-07 | THU | 26-Aug-07 | 1-Sep-07 |
| 31-Aug-07 | FRI | 26-Aug-07 | 1-Sep-07 |
| 1-Sep-07 | SAT | 26-Aug-07 | 1-Sep-07 |
| 2-Sep-07 | SUN | 2-Sep-07 | 8-Sep-07 |
| 3-Sep-07 | MON | 2-Sep-07 | 8-Sep-07 |
| 4-Sep-07 | TUE | 2-Sep-07 | 8-Sep-07 |
| 5-Sep-07 | WED | 2-Sep-07 | 8-Sep-07 |
| 6-Sep-07 | THU | 2-Sep-07 | 8-Sep-07 |
| 7-Sep-07 | FRI | 2-Sep-07 | 8-Sep-07 |
| 8-Sep-07 | SAT | 2-Sep-07 | 8-Sep-07 |
| 9-Sep-07 | SUN | 9-Sep-07 | 15-Sep-07 |
| 10-Sep-07 | MON | 9-Sep-07 | 15-Sep-07 |
| 11-Sep-07 | TUE | 9-Sep-07 | 15-Sep-07 |
I (Want to) Know What You Did Last Week
Knowing what happened this week is interesting. But I won’t stop here. π It is often more interesting to know what happened last week, since that week is done and in the books. So I would like to make objects named Start of Last Week and End of Last Week. Since I have the objects for this week built, the objects for last week are trivial. Here they are:
Start of Last Week
@Select('Dynamic Dates\Start of This Week') - 7
End of Last Week
@Select('Dynamic Dates\End of This Week') - 7
I plan to talk more about the @Select() function in a future post. For now I will just say that it is used to copy the select clause from another object referenced by the ‘Class Name\Object Name’ format. Since the Start of Last Week is exactly 7 days prior to the Start of This Week, I think that this is a perfect opportunity to use that feature. Since I have already created the complicated formulas for the start and end of the current week, I can reuse them / adjust them for the last week values as well. Using this function means that future changes to the objects for this week will be inherited by the objects for last week at the same time.
Summary
I can easily extend the formulas that I have provided so far. At this point I have a Dynamic Dates class that contains objects for Today, Yesterday, Start of This Week, and End of This Week. To do “next week” I would add seven days to the current week objects instead of subtracting. If I wanted to be creative I could prompt the user for the “number of weeks” and end up with a set of objects that would allow a report to run for 2 weeks ahead or 3 weeks prior just by changing a prompt value. All of these objects are based on sysdate and therefore can be used to provide dynamic date conditions for any sort of report. Their expected use is for scheduled reports but I cannot see anything wrong with using them interactively as well.
Remember that these objects as presented will not parse in Designer. They don’t reference a table and therefore will generate an error. As long as these objects are used with at least one “real” object on a query they will function perfectly fine.
Days and weeks are generally the same no matter what type of calendar is being used. In the next post in this series I will talk about how to work with monthly boundaries. If you need to work with fiscal periods you will have to include a calendar table of some sort, but I can do months with standard functions. That post will be published… next month. π
Related Posts
Oracle functions used in this post
- next_day() Returns the date of the next day based on the provided day name argument
- to_char() Converts a date into a string value with a wide variety of formats
- trunc() As used in this article it returns a date value with the time truncated off
Dynamic Dates With Oracle Functions
| Date | Formula |
|---|---|
| Today | trunc(sysdate) |
| Yesterday | trunc(sysdate-1) |
| Start of This Week | next_day(trunc(sysdate-7), ‘Sunday’) |
| End of This Week | next_day(case to_char(sysdate,’Day’) when ‘Saturday’ then trunc(sysdate-1) else trunc(sysdate) end, ‘Saturday’) |
| Start of Last Week | @Select(‘Dynamic Dates\Start of This Week’) – 7 |
| End of Last Week | @Select(‘Dynamic Dates\End of This Week’) – 7 |
Hi Dave,
I think that there is a mistake in the summary table at the end of the article:
next_day(trunc(sysdate-7), Γ’β¬ΛSundayΓ’β¬β’)will not give “End of This Week”. It gives the start of this week. Could you please check?Whatever are you talking about? *whistles nonchalantly*
π
Hi Dave,
Would you advocate that when compared to having a dedicated calendar table, using date functions (oracle or otherwise) are the best method?
If not, I think this ought to be mentioned for anyone who’s in the position of shaping/reshaping the underlying data structure, as my personal thoughts are that without that as a disclaimer, newbies to BOBJ and/or Data Warehousing could unwhittingly get led astray.
With that said, keep up the good work,on boh this website and the BOBJ forum.
Cheers
Wilf
I tried to get the End of Date formula to work but I ran into problems. Since it was the end of a long day, I didn’t really want to figure out what I’d messed up. So instead, I looked at the start of the week formula and just put +6 after.
Since the Start of Week formula is simple, it made the whole process much easier.
When I ran the data, it came back perfectly, just a thought on simplifying this EXTREMELY helpful set of formulas.
Thank you very much for the time,
Aron
Aron, there’s most likely nothing wrong with that solution at all. π Glad you found my ramblings useful.
Dev,
This is my requirement, Pls help me
If I refresh the Report on May 5th 2008 I should see a report for
Current week – April 27th – May 3rd
Previous Week – Aprl 20th – April 26th
Ganesh, your weekly ranges are simply the “last week” as already outlined in the content of the blog post, and the week prior to that. Take the “last week” range and subtract 7 from each and you’ll get what you are calling a “previous week” for your requirements.
Hi dave,
I need to find out week from a given date for example
01/01/2010
i need to find week ,current week, previous week
the output will like current week 32nd week (of the year)
i am using BOXIR2 and Teradata as database
can anyone help me? It is very urgent requirement.
i have gone through your post but it is for oracle can you help me with teradata
Hi, “teddy”, I see that you have posted the same question on BOB where you are likely to get faster responses than here.
Hi dave,
I dint get any reply over there π and i need it urgently if you know the answer can you please help me?
thanks in advance
teddy
I don’t have an immediate answer, that’s my point. π If you have an urgent question, posting on BOB or SCN is far more likely to generate an answer because either someone will know the answer, or someone might be interested enough to do the research and help you answer the question.
Do you have a calendar table in your system? That’s often a good place to start. The functions shown in this series of posts are designed to work without a calendar table. Teradata has the built-in calendar sys_calendar.calendar that you might be able to use in your case.
That’s as far as I am going to go here. Thanks for understanding.
Hi Dave,
Thanks for your reply. Even though i am playing with Teradata and BOXIR2 i dnt have option to use calendar table. Thatβs how i reached in your blog :). Hoping some where i can find the answer :). I had search in BOB, many of option are including the calendar table. Anyway as of now i got an option in bo we can use week (transaction date)
Eg:
week: week (19/08/2006)
Result will be 33 π
Previous week: week (19/08/2006)-1
Result will be 32 π
I need to do more research in it.
Thank you π
teddy
Here is a different approach to solve a problem I had. I don’t have access to the sql portion using “Next_Day()”. So, I had to generate a start and end of week using objects in the report designer.
First off I had to change the day of week to start on sunday instead of monday:
Name of variable object: DayOfWeek
designer code:
= If (DayNumberOfWeek() =7)Then ( 1 ) Else DayNumberOfWeek()+1Then from there I was able to determine the first of the week and end of the week based of the date used.
Name of variable object: EndOfWeek
designer code:
=-+7Name of variable object: StartOfWeek
designer code:
=-+1Hope this was helpful.
Gary, you may need to read my “posting code” notes because I think some of your comment was eaten by WordPress…
Hi Dave,
I want to calculate trend based on the comparision made b/w present month and last month…
I am able to comapre it using alters and displaying trend icons ( up & down arrow)..however currently i have hardcoded the query filter like current month= feb-11 and last month=jan-11 in two diff data providers.
Here is the requirement:-
I have two dp’s:
1) current month : it will contain prompt on “month name” which user will enter
2) previous month : it will contain filter on object “last month” which i have created using formula add_months(month, -1) in oracle…
I now want that user shud only enter feb-11 ( in prompt for 1st dp) however in the 2nd dp it shud auto pass “jan-11” for last month..so it will calculate data for last month..
Basically i want to pass current month name in 1st and it shud pass last month automatically in 2nd dp…
Is there any trick at universe level???
Plss let me know how we can do this….
If you are already splitting into two data providers, I’m not sure why the existing objects won’t work. A “current month” object prompts for a month. A “prior month” object prompts for the same thing, but offsets by one month. Using the proper condition object in each data provider should provide what I think you are requesting.
please tell me the formulas in case of sql server 2008
Hi, Ganesh, if you click the Dynamic Dates link under my category list it will show all of the posts in this series, including one at the end where I linked to some other resources. The specific post is Dynamic Dates Part IV: More Database and it includes a link to another blog with some SQL Server information. Welcome, and thanks for your question.
Hi Dave,
Thanks for your blog and its helps lot personally and professionally. I am stuck with the Last Week and Current Week conditions at the universe level. I am using MS SQL 2008 and my week starts from Sunday according to DB default but the business wants the week beginning from Monday. I have referred your blogs and tried to implement various logics but couldn’t able to break this.
Today is 19th July 2013 and i want to report Current Week, Last Week, Curr Week to Date, Last WTD figures.
i have created a condition like this for Last week… It takes into account (Sunday to Saturday) which is 7th July 2013 to 13th of July but i want Monday to Sunday i.e. (8th to 14th of July 2013). Please help me on this.
case when datepart(wk,getdate())=1 then 52 else datepart(wk,getdate())-1 end = datepart(wk,dbo.SAP_SALES_DATA.SL_VOUCHER_DATE) and case when datepart(wk,getdate())=1 then datepart(yyyy,getdate())-1 else datepart(yyyy,getdate()) end = datepart(yyyy,dbo.SAP_SALES_DATA.SL_VOUCHER_DATE).
Prasanna
Need to display Week range for given date prompt. On the report header of webi report
in business object. We use Calender Table and the
Week start on Sunday and ends on Saturday
Eg : If Date prompt is : 01/23/2014
Then it should display : 01/19/2014 to 01/25/2014
Eg : If Date prompt is : 01/29/2014
Then it should display : 01/26/2014 to 01/31/2014 with respective to the month of the date prompt
But not : 01/26/2014 to 02/01/2014
Thnanks for the reply. π
I have requirement need help … I need to make filters like last 2 months and last year.
I have a dervied table which is being used for Aggregated tables can some one suggest how i can make them as “Derived Table”.YEAR=trunc((trunc(sysdate,’YYYY’)-1),’YYYY’) & extract (month from “Derived Table”.MONTHOFYEAR) between
(extract(month from “Derived Table”.MONTHOFYEAR)-1) and (extract(month from “Derived Table”.MONTHOFYEAR))
Sql for the derived table is as follows
select distinct monthofyear,year from rep_time_dim
Hi Dave,
I saw many useful scenarios from your blog. I need some help. Please help me on that.
I wanted to create frequency with the LOV’s Daily,Weekly,Monthly,Quarterly,6months,12 Months(Annual of last year),24-Months,YTD.
2. for example if I have scheduled a report on weekly basis it should display last 7days of data. If the report got failed yesterday then we have to pass 1(By default it is zero) so it displays last week data. Override( Means, If it is one it will give one day back data, if it is 2 it will give 2 days back data based on Frequency value)
Suppose I have frequency parameter it should display all frequencies. 2. I should have Override parameter should take value from the user and goes back to number of days based on frequency. Please help me how to do it