General OBIEE

Get Involved. Join the Conversation.

Topic

    Cassandra Kurth
    Something off in SQL expression for criteria
    Topic posted June 8, 2016 by Cassandra Kurth, tagged Analyses, SQL 
    89 Views, 1 Comment
    Title:
    Something off in SQL expression for criteria
    Summary:
    Trying to capture last month's data
    Content:

    The report functions with the expressions below; however, It doesn't seem to actually capture the last day of the previous month. I run the data for May and know there are results for 5/31 but only captures up to 5/29. What is wrong with the expressions. The Beginning of Previous Month expression seems to capture from 5/2 and I have verified that is correct.

    Beginning of Previous Month

    TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

    End of Previous Month

    TIMESTAMPADD(SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH(CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

     

    Thank you in advance for any assistance!

    Cassie Kurth

    Comment

     

    • Noelle Bartlam

      Hi Cassie,

      Using the syntax you have above I'm not seeing any issues running this on an internal env.  I get 5/31/2016 for the End of Previous Month.

      Noelle