OraFAQ Forum: SQL & PL/SQL » Previous Quarter three months of Start Date and End Dates

Home » SQL & PL/SQL » SQL & PL/SQL » Previous Quarter three months of Start Date and End Dates (SQL)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Previous Quarter three months of Start Date and End Dates [message #690125]

Wed, 16 October 2024 12:35

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

Report message to a moderator

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

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

What SQLs have you tried so far?

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13958
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?

Report message to a moderator

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

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

Report message to a moderator

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

cookiemonster
Messages: 13958
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?

Report message to a moderator

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

cookiemonster
Messages: 13958
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.

Report message to a moderator

Previous Topic:	how to select Sub_Query fields
Next Topic:	Oracle Percentage by Rank(Top Percentages)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 03:45:14 CST 2024