8 Replies Latest reply on Oct 8, 2015 9:32 AM by Hgnis-Oracle

    Find the 2nd Sunday of each month in a report

    Hgnis-Oracle

      Hi,

       

      I have the data of EMP_Name and Join_Date. I have to build a dashboard report where

       

      1. In the dashboard, there is a prompt. If I select a year and a month then, the report should show only respective month's data.
      2. That respective report will show those employees who joined the company only after 2nd Sunday of each month till end of the month.

       

      Any inputs/suggestion, how to build this ?

      Thanks in advance.

        • 1. Re: Find the 2nd Sunday of each month in a report
          Gianni Ceresa

          Hi,

          Are you asking how to filter your analysis based on the value of the prompt? If you are this point I would highly suggest to get a training or a book and a week off to read it and practice a bit ...

           

          For the 2nd Sunday have a look at this: OBIEE 11g: How to find the 2nd friday of the month

          Friday, Sunday, same deal, same logic, just a small change ...

          • 2. Re: Find the 2nd Sunday of each month in a report
            Hgnis-Oracle

            Thanks for your reply. I have the query for getting the 2nd Sunday. I was bit worried if we choose a month, whether it will pass the month value to the sql query or not to get the result for that particular month only. Otherwise, for the current month, I had this solutions.

             

            select ename,CREATE_DATE from EMP

            where trunc(create_date) > NEXT_DAY(NEXT_DAY(TRUNC(CREATE_DATE, 'MONTH') - 1, 'Sunday'), 'Sunday');

             

            Thanks again!!

            • 3. Re: Find the 2nd Sunday of each month in a report
              Hgnis-Oracle

              Gianni,

              I tried with the provided query. I modified to get the 3rd Friday of each month as

               

              TIMESTAMPADD(SQL_TSI_DAY, (13+6-DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(POD_D.CREATE_DATE), POD_D.CREATE_DATE))), TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(POD_D.CREATE_DATE), POD_D.CREATE_DATE))

               

              It works fine for the month Feb (2/20/2015 12:00:00 AM), Mar (3/20/2015 12:00:00 AM), May (5/15/2015 12:00:00 AM), June (6/19/2015 12:00:00 AM) , July (7/17/2015 12:00:00 AM) 2015. But failed for the month of August 2015. The 3rd Friday for the month August is 21st, but this above query returns as 14th August. - 8/14/2015 12:00:00 AM

               

              Thanks

              • 4. Re: Find the 2nd Sunday of each month in a report
                Gianni Ceresa

                You have a query, yes ... a SQL query .... OBIEE doesn't speak SQL but LSQL, so your query will not really be usable like that ...

                 

                You are right, it looks like when the first day of the month is a Saturday it skip 1 week...

                The correct formula for the 3rd Friday is:

                 

                TIMESTAMPADD(SQL_TSI_DAY, (14+6-CASE WHEN DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE)) = 7 THEN 0 ELSE DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE)) END), TIMESTAMPADD(SQL_TSI_DAY, 1-DAYOFMONTH(CURRENT_DATE), CURRENT_DATE))

                 

                If instead of CURRENT_DATE you want to use your own date column just replace it in the formula and it works.

                • 5. Re: Find the 2nd Sunday of each month in a report
                  Hgnis-Oracle

                  Thank you very much Gianni. Tested it and this worked fine.

                  • 6. Re: Find the 2nd Sunday of each month in a report
                    Hgnis-Oracle

                    Hi Gianni,

                         If you don't mind, May I ask for your inputs further related to this query ?

                    Thank you.

                    • 7. Re: Find the 2nd Sunday of each month in a report
                      Gianni Ceresa

                      Inputs on what? Wasn't it all about finding the 2nd (or 3rd) Sunday of the month?

                      • 8. Re: Find the 2nd Sunday of each month in a report
                        Hgnis-Oracle

                        That 2nd approach was the correct solution and it was solved. Thank you for that.

                        I have posted another query related to this.

                        Default value for Prompt

                         

                        My idea to solve this is like
                        I will compare between the sysdate and 3rd Friday of each month.

                        If the current date is less than 3rd Friday of that  month then it should show the last month for which the data is available

                        or else it should show the current month.

                         

                        I have a monthly report and  a prompt for this. By default, in the prompt, it should show the current month. If the data is not available for the current month then the prompt should show last available data month name in the prompt.

                        What is the approach to do this ?

                         

                        I have done something like in the "Default to > SQL result" in the prompt

                         

                        select  cast(MONTHNAME(CREATE_DATE) as varchar(40)) from  SA order by CREATE_DATE desc

                         

                        The above query gives me the current month value in the prompt. It is fine. However, in case, there is no data to show for current month (say OCT), then it should select past available month for which data is available (say Sep or Aug)

                         

                        Thank you.