Friday, April 4, 2014

Difference Between Two Dates Excluding Saturday and Sunday At Report Level In OBIEE 11g

Hey Guys,

Recently we got a requirement to find out the difference between two dates excluding the weekends i.e. Saturday and Sunday. I'm giving the report level formula that can be used to achieve this. Here the Presentation table I'm using is "Time" and the respective date columns are "Start Date" and "End Date". You need to replace these column names according to your Presentation Layer.

-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END

Hope this helps!!!

Cheers,
Siva
at
Labels: , , , , , , , ,

21 comments:

  1. 21st Century Software SolutionsSeptember 8, 2014 at 1:42 AM

    BI Apps Online Training, http://www.21cssindia.com/courses/biapps-online-training-19.html BI Apps Online Training, BI Apps Training, BI Apps, BI Apps Online Training| BI Apps Training| BI Apps| "Courses at 21st Century Software Solutions
    Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 - contact@21cssindia.com
    Visit: http://www.21cssindia.com/courses.html"

    ReplyDelete
  2. SagarJuly 13, 2015 at 11:24 AM

    Hi Shiva, I too have same requirement and trying to use above formula and getting unexpected result. For example start date is 7/3/2015 and End date is 7/13/2015 then the date difference should be 6, but am getting -4. Can you let me know if i am doing anything wrong.

    Thanks,
    Sagar

    ReplyDelete
    Replies
    1. Siva PratapJuly 14, 2015 at 6:17 AM

      It's giving 6 for me Sagar, Please check if your formula has any issues. I would start checking the paranthesis.

      Delete
    2. SagarJuly 14, 2015 at 1:55 PM

      Siva, I used CURRENT_DATE (7/13/2015) as Time.End Date and date column from time dimension for Time. Start Date (7/3/2015) and i am still getting -4. Can you let me know where do i need to make Changes.

      Delete
    3. Siva PratapJuly 15, 2015 at 2:58 AM

      Sagar, Can you add two more columns in your report with CURRENT_DATE and Time.Start Date. Please check what's the format of the date displayed in the results for both those columns.

      Delete
    4. SagarJuly 15, 2015 at 9:56 PM

      Both column values are in the format of MM/DD/YYYY (7/15/2015).

      Delete
    5. Siva PratapAugust 13, 2015 at 2:26 AM

      Then it should work perfectly fine Sagar!! You might want to split the formula into pieces and put them in different columns to check the values. Let me know.

      Delete
  3. AntexityDecember 7, 2015 at 11:47 AM

    Hey Siva,

    Im getting the same issues as Sagar. Heres my formula:

    TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max End Date for Warranty Claim"))-1),max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max End Date for Warranty Claim")),

    TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max Start Date for Warranty Claim")))-1),

    TIMESTAMPADD(SQL_TSI_DAY,6,max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max Start Date for Warranty Claim"))))/7*5+
    MOD(7-DAYOFWEEK(max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max Start Date for Warranty Claim")),6)+

    CASE WHEN DAYOFWEEK(max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max End Date for Warranty Claim"))-2>5 THEN 5 ELSE DAYOFWEEK(max("Manufacturing - Work Order"."Asset Master Hist"."(History) Max End Date for Warranty Claim"))-2 END

    ReplyDelete
    Replies
    1. Siva PratapDecember 23, 2015 at 4:33 AM

      Can you try to split each piece and see what are the values being returned in OBIEE? That help in debugging. Also, you are using max function for your dates which might create an issue. But, I would first start splitting it up.

      Siva

      Delete
  4. AnonymousMay 4, 2016 at 5:55 AM

    How can we exclude Holidays ???? Any Idea, Please.....

    ReplyDelete
    Replies
    1. UnknownJanuary 17, 2018 at 5:52 AM

      Hi Arun
      Did you find solution for excluding Holidays

      Delete
  5. MichalMarch 8, 2017 at 12:10 AM

    Siva, Antexity and Sagar, there should be minus (-) before the timestampdiff part

    ReplyDelete
    Replies
    1. Jesse KremerMay 4, 2017 at 11:10 PM

      Champion. For those who come along in the future this is the full copy/paste code:

      -TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
      TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
      MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END

      Delete
  6. UnknownJuly 12, 2017 at 3:34 PM

    how to change the formula if we get the answer in minutes?

    ReplyDelete
  7. UnknownMay 23, 2018 at 3:25 PM

    This comment has been removed by the author.

    ReplyDelete
  8. UnknownMay 23, 2018 at 3:28 PM

    I am using below formula to get difference between two dates with minutes. Can anyone help me to write a formula to get the difference between two dates with minutes which excludes weekends ? Please.

    FLOOR(
    TIMESTAMPDIFF( SQL_TSI_MINUTE , MIN("Review"."Review Group Start Date"), MAX("Review"."Review Group End Date"))
    / (24 * 60)
    ) +1

    ReplyDelete
  9. Vale Co XeniaNovember 3, 2019 at 9:01 PM

    Great Article. As I read the blog I felt a tug on the heartstrings. it exhibits how much effort has been put into this.
    IEEE Projects for CSE in Big Data

    Spring Framework Corporate TRaining

    Final Year Project Centers in Chennai

    JavaScript Training in Chennai

    ReplyDelete
  10. UnknownMarch 25, 2022 at 8:02 AM

    Hi,
    Is it possible to explain a bit of the logic in this query?

    I understand some of it but not a lot

    ReplyDelete
  11. AnonymousMay 16, 2022 at 1:54 AM

    fon perde modelleri
    MOBİL ONAY
    mobil ödeme bozdurma
    nft nasıl alınır
    ankara evden eve nakliyat
    TRAFİK SİGORTASI
    dedektor
    WEB SİTE KURMA
    Ask Romanlari

    ReplyDelete
  12. AnonymousMay 25, 2022 at 9:38 PM

    smm panel
    smm panel
    İS İLANLARİ
    instagram takipçi satın al
    hirdavatciburada.com
    beyazesyateknikservisi.com.tr
    Servis
    tiktok jeton hilesi

    ReplyDelete

Subscribe to: Post Comments (Atom)

ORA-01719: outer join operator (+) not allowed in operand of OR or IN after upgrading to ODI 12c

Hello All, I'm writing here after a long gap. Hope you all are doing great, and will continue to read my posts!! Recently, after upgradi...