SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: DaysBetween ??

  1. #1
    SitePoint Zealot avolia's Avatar
    Join Date
    Jun 2001
    Location
    USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DaysBetween ??

    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???

    Thanks.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    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.

    see http://searchdatabase.techtarget.com...285649,00.html
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot avolia's Avatar
    Join Date
    Jun 2001
    Location
    USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?
    NetMaxx.com: WordPress Premium Themes Directory

  4. #4
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    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!

  5. #5
    SitePoint Zealot avolia's Avatar
    Join Date
    Jun 2001
    Location
    USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, businessobjects can use any standard DB backend. This one is using Oracle.
    NetMaxx.com: WordPress Premium Themes Directory

  6. #6
    SitePoint Zealot avolia's Avatar
    Join Date
    Jun 2001
    Location
    USA
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok this is what I found from BusinessObjects discussion forum < http://www.forumtopics.com/busobj/viewforum.php?f=35 >

    Code:
    =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)
    I have tested it and it work [img]images/smilies/smile.gif[/img]


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •