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 ...
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');
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
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.
Thank you very much Gianni. Tested it and this worked fine.
If you don't mind, May I ask for your inputs further related to this query ?
Inputs on what? Wasn't it all about finding the 2nd (or 3rd) Sunday of the month?
That 2nd approach was the correct solution and it was solved. Thank you for that.
I have posted another query related to this.
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)