Results 1 to 6 of 6
Thread: DaysBetween ??
Apr 21, 2003, 12:45 #1
I have been struggling with this for a while and I can't find and solution since I am not that profession with SQL.
I am using BusinessObjects to create a report where I need to figure out the total number of days between two different dates EXCLUDING weekends. (that is where I am having problem "weekends" )
in business objects I can use this function
=DaysBetween(<Application Dt> ,<Due Dt> )
Now I want to exclude weekends from the results. Any Idea???
Apr 21, 2003, 13:50 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 63 Post(s)
- 3 Thread(s)
that's tricky in pure sql
how big a date range should be covered? if only a few years, i suggest a calendar table
this can easily be created and pre-populated with not only weekends but also special company closings, statutory holidays, etc.
Apr 21, 2003, 14:58 #3
The problem with business objects is there is no way at least that I know of to creat a new table.
Is there a different way of achiving this?
Apr 21, 2003, 15:36 #4
- Join Date
- Jan 2002
- 0 Post(s)
- 0 Thread(s)
Does BusinessObjects use a standard DB backend (like SQL2000 for instannce)?MarcusJT
- former ASP web developer / former SPF "ASP Guru"
- *very* old blog with some useful ASP code
- Please think, Google, and search these forums before posting!
Apr 21, 2003, 16:08 #5
Yes, businessobjects can use any standard DB backend. This one is using Oracle.
Apr 24, 2003, 08:28 #6
Ok this is what I found from BusinessObjects discussion forum < http://www.forumtopics.com/busobj/viewforum.php?f=35 >
=Floor(DaysBetween(<Application Dt> ,<Subsequent Due Dt>)/7)*5+ (If DayNumberOfWeek(<Subsequent Due Dt>)>=DayNumberOfWeek(<Application Dt>) Then DayNumberOfWeek(<Subsequent Due Dt>)-DayNumberOfWeek(<Application Dt>) Else DayNumberOfWeek(<Subsequent Due Dt>)-DayNumberOfWeek(<Application Dt>)+5)