Display Current Quarter, Previous Quarter and same quarter of previous Year in a crosstab

[vaibhav_rathore]

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

[nscheaffer]

What database are you using (e.g., SQL Server, Oracle, etc.)?

[vaibhav_rathore]

noel.scheaffer Oracle is being used here.

[nscheaffer]

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

[vaibhav_rathore]

This looks great noel.scheaffer That will be of great help if you can share this in Oracle code.
Thank You again 🙂

[nscheaffer]

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.

[vaibhav_rathore]

Thank you noel.scheaffer I will try this one.

[vaibhav_rathore]

Hi noel.scheaffer If you can share the oracle version of this code that will be very helpful.

I tried from my end but its not working.

Thanks

Vaibhav

[nscheaffer]

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?

[vaibhav_rathore]

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.

[nscheaffer]

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.