Home » SQL & PL/SQL » SQL & PL/SQL » Previous Quarter three months of Start Date and End Dates (SQL)
Previous Quarter three months of Start Date and End Dates [message #690125] Wed, 16 October 2024 12:35 Go to next message
suji6281
Messages: 151
Registered: September 2014
Senior Member
Hi All,

Can you please help me with the SQL with the following scenarios.

1. SQL should return previous three months start and end dates when current month is APR.
   Output:
            1-Jan-2024, 31-Jan-2024
            1-Feb-2024, 28-Feb-2024
            1-Mar-2024, 31-Mar-2024 
2. SQL should return previous three months start and end dates when current month is JUL.
   Output:    
            1-Apr-2024, 30-Apr-2024
            1-May-2024, 31-May-2024
            1-Jun-2024, 30-Jun-2024    
3. SQL should return previous three months start and end dates when current month is OCT.
   Output:
 
            1-Jul-2024, 31-Jul-2024
            1-Aug-2024, 31-Aug-2024
            1-Sep-2024, 30-Sep-2024   
4. SQL should return previous two months start and end dates when current month is DEC.
   Output:
 
            1-Oct-2024, 31-Oct-2024
            1-Nov-2024, 30-Nov-2024   
5. SQL should return previous Year and month start and end dates when current month is JAN.
   Output:
 
            1-Dec-2024, 31-Dec-2024   
I am not sure is this possible with single SQL query. If Yes please help me with SQL.

Regards
Suji
          
Re: Previous Quarter three months of Start Date and End Dates [message #690126 is a reply to message #690125] Wed, 16 October 2024 12:37 Go to previous messageGo to next message
John Watson
Messages: 8958
Registered: January 2010
Location: Global Village
Senior Member
What SQLs have you tried so far?
Re: Previous Quarter three months of Start Date and End Dates [message #690127 is a reply to message #690126] Wed, 16 October 2024 12:42 Go to previous messageGo to next message
suji6281
Messages: 151
Registered: September 2014
Senior Member
Hi John,

I have tried with this below SQL.
SELECT to_char(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3), 'Q') prev_qtr_no,
ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) AS First_Start, last_day(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3)) as First_Last,
ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -2) AS Second_Start, last_day(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -2)) AS Second_Last,
ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -1) AS Third_Start, last_day(ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -1)) AS Third_Last FROM DUAL;

Regards
Suji
Re: Previous Quarter three months of Start Date and End Dates [message #690128 is a reply to message #690127] Thu, 17 October 2024 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well that sql will output 7 columns, while your examples suggest you want 2 columns. So which do you want?
And what should the output be for the other 7 months you've not listed above?
Re: Previous Quarter three months of Start Date and End Dates [message #690129 is a reply to message #690128] Thu, 17 October 2024 05:33 Go to previous messageGo to next message
suji6281
Messages: 151
Registered: September 2014
Senior Member
Hi Cookiemonster,

This should be fine with 2 columns or 7 Columns. However, I have completed the SQL with all 12 months as select parameters.
Thank you for your support.

Regards
Suji
Re: Previous Quarter three months of Start Date and End Dates [message #690130 is a reply to message #690129] Thu, 17 October 2024 06:27 Go to previous messageGo to next message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
In your first post you stating what the output should be when the current month is Apr, Jul, Oct, Dec or Jan.
Are you going to tell us what the output should be when the current month is one of the other 7 months in the year, or were you expecting us to guess?
Re: Previous Quarter three months of Start Date and End Dates [message #690131 is a reply to message #690130] Thu, 17 October 2024 06:31 Go to previous message
cookiemonster
Messages: 13952
Registered: September 2008
Location: Rainy Manchester
Senior Member
And given that in your original examples the number of rows returned differed in different months, saying it will be fine with 2 columns or 7 is fairly unhelpful. For example - for JAN, which of the 7 columns should be populated, given your example output only has 2 values?

Just tell us what output you want, and what logic should be used to determine that output and we can give you the SQL to do it.
Previous Topic: how to select Sub_Query fields
Next Topic: Oracle Percentage by Rank(Top Percentages)
Goto Forum:
  


Current Time: Fri Nov 08 17:44:15 CST 2024