on 10-10-2023 9:28 AM
Hi Experts,
i have requirement where i would like to display Current Quarter, Previous Quarter and same quarter of previous Year in a crosstab.
Currently its coming all the quarters for all the dates available.
formula used in IDT to concat Year and Quarter:Year_Quarter Dim=
concat(concat(TIME_DIMENSION_DIM.CALENDAR_YEAR,'-'),substr(TIME_DIMENSION_DIM.CALENDAR_QUARTER,0,2))
requirement is:
For example: if users selects 2023-4 in prompt then it must display the quantity for
2023-4, 2023-3 and 2022-4
and the Year-Quarter will be selected by user in prompt, whatever Year-Quarter he selects the Year-Quarter must change dynamically showing the current quarter of the selected year followed by previous quarter and same quarter of previous Year
Please suggest how can i do the same.
Thank you all
There are likely numerous ways to solve this.
One simple way would be to create a free-hand SQL query that prompts for a date in the current quarter and then calculate the current quarter, previous quarter, and same quarter last year.
Here it is in SQL Server syntax...
DECLARE
@ReferenceDate DATE
, @ReferenceDateMinusOneQuarter DATE
, @ReferenceDateMinusOneYear DATE;
SET @ReferenceDate = @Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0)
SET @ReferenceDateMinusOneQuarter = DATEADD (QUARTER, -1, @ReferenceDate);
SET @ReferenceDateMinusOneYear = DATEADD (YEAR, -1, @ReferenceDate);
SELECT
@ReferenceDate AS [Reference Date]
, CONVERT (CHAR(4), YEAR (@ReferenceDate)) + '-' + CONVERT (CHAR(1), DATEPART (q, @ReferenceDate)) AS [Reference Quarter]
UNION
SELECT
@ReferenceDateMinusOneQuarter
, CONVERT (CHAR(4), YEAR (@ReferenceDateMinusOneQuarter)) + '-'
+ CONVERT (CHAR(1), DATEPART (q, @ReferenceDateMinusOneQuarter))
UNION
SELECT
@ReferenceDateMinusOneYear
, CONVERT (CHAR(4), YEAR (@ReferenceDateMinusOneYear)) + '-'
+ CONVERT (CHAR(1), DATEPART (q, @ReferenceDateMinusOneYear))
I will try to convert that to Oracle when I have more time.
Here are my results based on today's date...
Once you refresh (or run) that free-hand SQL query you can use the results in your main query and get only the quarters you want.
Does that make sense?
Noel
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Here is one possibility in Oracle SQL...
select sysdate as "Reference Date"
, to_char(sysdate, 'YYYY-Q') as "Reference Quarter"
from dual
union
select add_months(sysdate, -3)
, to_char(add_months(sysdate, -3), 'YYYY-Q')
from dual
union
select add_months(sysdate, -12)
, to_char(add_months(sysdate, -12), 'YYYY-Q')
from dual;
I cannot figure out how to use variables to make this equivalent to my SQL Server version.
I don't have an Oracle instance available to me so I am using https://dbfiddle.uk set to 23c.
What is not working? Are you getting an error or just the wrong dates?
Hi noel.scheaffer The query you sent for oracle:
select sysdate as "Reference Date"
, to_char(sysdate, 'YYYY-Q') as "Reference Quarter"
from dual
union
select add_months(sysdate, -3)
, to_char(add_months(sysdate, -3), 'YYYY-Q')
from dual
union
select add_months(sysdate, -12)
, to_char(add_months(sysdate, -12), 'YYYY-Q')
from dual;
is working correctly but its on the current sysdate , the need is that the user will select date and based on selection the quarter will be displayed dynamically.
Thanks again for your help Noel.
You will need replace sysdate with the prompt. The to_char() Oracle function expects a date, but the Prompt will always return a string so you need to convert to a date with to_date() Oracle function.
The to_date() function needs to know what the format or your string is so it know how to convert it. What format should you use? I don't know. To figure this out I created this simple free-hand SQL...
select @Prompt('Enter Date','DT',,Mono,Free,Persistent,,User:0) as "Reference Date"
from dual
And got this...
How do you tell Oracle that is your format? I don't know. I Googled "oracle to_date format" and came across this. Given that, it seemed 'DD/MM/YYYY HH24:MI:SS' would work. So I tried it. And it worked. I put it all together like this...
select to_date(@Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0), 'DD/MM/YYYY HH24:MI:SS') as "Reference Date"
, to_char(to_date(@Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0), 'DD/MM/YYYY HH24:MI:SS'), 'YYYY-Q') as "Reference Quarter"
from dual
union
select add_months(to_date(@Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0), 'DD/MM/YYYY HH24:MI:SS'), -3)
, to_char(add_months(to_date(@Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0), 'DD/MM/YYYY HH24:MI:SS'), -3), 'YYYY-Q')
from dual
union
select add_months(to_date(@Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0), 'DD/MM/YYYY HH24:MI:SS'), -12)
, to_char(add_months(to_date(@Prompt('Enter Date','D',,Mono,Free,Persistent,,User:0), 'DD/MM/YYYY HH24:MI:SS'), -12), 'YYYY-Q')
from dual
Entering a prompt value of yesterday, October 18, 2023, I get this.
You have got to experiment to get to a solution that works for you. I hope this helps you meet your requirement. If not, I cannot help anymore. Perhaps someone else can.
User | Count |
---|---|
76 | |
9 | |
8 | |
6 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.