| finding previous quarter end date [message #655384] |
Tue, 30 August 2016 05:13  |
 |
lappi
Messages: 8
Registered: March 2016
Location: New york
|
Junior Member |
|
|
Hi,
I have two input dates as parameters and my query shoud return previous quarter end dates between two dates.
We have four quarters in a year.
Q1 -Jan to March
Q2-Apr to June
Q3-Jul to Sep
Q4-Oct to Dec
Input dates example 'Q1-2015','Q2=2016'
Input Dates
Q1-2015 , Q2-2016
Number of Dates to be returned from query
Dec 31st 2014
March 31st 2015
June 30 2015
Sep 30 2015
Dec 31st 2015
March 31st 2016
Input Dates
Q1-2016 Q2-2016
Number of Dates to be returned from query
Dec 31st 2015
March 31 2016
I need it to be returned in a query.
Thanks for your help.
|
|
|
|
| Re: finding previous quarter end date [message #655387 is a reply to message #655384] |
Tue, 30 August 2016 05:46  |
John Watson
Messages: 8930
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
You should be able to do something with the Q date format specifier. For example,orclz> select sysdate,to_char(sysdate,'q'),trunc(sysdate,'Q'),trunc(sysdate,'Q')-1 from dual;
SYSDATE T TRUNC(SYSD TRUNC(SYSD
---------- - ---------- ----------
2016-08-30 3 2016-07-01 2016-06-30
orclz>
--update: expanded the example a bit
[Updated on: Tue, 30 August 2016 05:48] Report message to a moderator |
|
|
|
| Re: finding previous quarter end date [message #655390 is a reply to message #655384] |
Tue, 30 August 2016 06:06 |
 |
Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
From your previous topic:
Quote:
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also FEEDBACK to your topics, review your previous one, tell how it helps, what is the solution you ended with, thank people who spent time to help you.
[Updated on: Tue, 30 August 2016 06:07] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: finding previous quarter end date [message #655405 is a reply to message #655404] |
Tue, 30 August 2016 10:30 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
The easiest way is to setup a date table where the primary key is your quarter code and the other 2 columns show the start and end of the quarter. see below the code to insert into the table. I choose an arbitrary date to start of 01/01/2000. If you need it earlier then simply modify the query.
select 'Q'||to_char(beg_quarter,'q-yyyy') REF_CODE,BEG_QUARTER,END_QUARTER
from
(
SELECT ADD_MONTHS (DATE '2000-01-01', (LEVEL - 1) * 3) Beg_quarter,
ADD_MONTHS (DATE '2000-01-01', (LEVEL - 1) * 3 + 3) - 1 End_quarter
FROM DUAL
CONNECT BY LEVEL <= 160);
Then you do something as simple as
select beg_quarter,end_quarter
from my_date_table
where ref_code = <input code>;
[Updated on: Tue, 30 August 2016 10:32] Report message to a moderator |
|
|
|
|
|
| Re: finding previous quarter end date [message #655418 is a reply to message #655384] |
Tue, 30 August 2016 18:08 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
SCOTT@orcl_12.1.0.2.0> variable start_date varchar2(7)
SCOTT@orcl_12.1.0.2.0> variable end_date varchar2(7)
SCOTT@orcl_12.1.0.2.0> exec :start_date := 'Q1-2015'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> exec :end_date := 'Q2-2016'
PL/SQL procedure successfully completed.
SCOTT@orcl_12.1.0.2.0> select to_char
2 (add_months
3 (to_date
4 (substr (:start_date, 2, 1) * 3 - 2 || '-01-' || substr (:start_date, 4),
5 'mm-dd-yyyy'),
6 (rownum - 1) * 3) - 1,
7 'Mon ddth yyyy') previous_quarter_end
8 from dual
9 connect by level <= 4 - substr (:start_date, 2, 1)
10 + substr (:end_date, 2, 1)
11 + ((substr (:end_date, 4) - substr (:start_date, 4) - 1) * 4)
12 + 1
13 /
PREVIOUS_QUARTER_END
----------------------
Dec 31st 2014
Mar 31st 2015
Jun 30th 2015
Sep 30th 2015
Dec 31st 2015
Mar 31st 2016
6 rows selected.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
