Home » SQL & PL/SQL » SQL & PL/SQL » Get Current & Previous Quarter Data from table
Get Current & Previous Quarter Data from table [message #668679] Fri, 09 March 2018 03:39 Go to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Hi ,
In my employee table I have the below data

Eff_dt	     Employee_id
31-Dec-16	12
31-Dec-16	234
31-Dec-16	351
31-Dec-16	234
30-Sep-16	78
30-Sep-16	89
30-Sep-16	163
30-jun-16	45
30-jun-16	526



I want to create a query to get current quarter & its previous quarter data based on the effective date passed.

For Example

If I pass 30 Sep 2016 then I should get Sep & Jun 2016 Data
Eff_dt         Employee_id
30-Sep-16	78
30-Sep-16	89
30-Sep-16	163
30-Jun-16	45
30-Jun-16	526

I have tried the below Query
 SELECT * FROM  ( 
SELECT * FROM employee WHERE effective_date BETWEEN add_months(effective_date,-3) AND effective_date 
ORDER BY effective_date
)WHERE effective_date = '30-Sep-2016'

But it is giving only Sep 2016 Data.


Could any one please help me.

[Updated on: Fri, 09 March 2018 03:41]

Report message to a moderator

Re: Get Current & Previous Quarter Data from table [message #668680 is a reply to message #668679] Fri, 09 March 2018 03:53 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Trunc() can take a parameter to return the quarter numbers. You should be able to get the logic from there on?
Re: Get Current & Previous Quarter Data from table [message #668681 is a reply to message #668680] Fri, 09 March 2018 04:06 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
Do you mean to apply trunc before effective date??

[Updated on: Fri, 09 March 2018 04:10]

Report message to a moderator

Re: Get Current & Previous Quarter Data from table [message #668683 is a reply to message #668681] Fri, 09 March 2018 04:10 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Something along these lines should point you in the right direction

  1  select trunc(sysdate,'Q'), 'THIS_Q' from dual
  2  union all
  3* select trunc(trunc(sysdate,'Q')-1,'Q'), 'LAST_Q' from dual
SQL> /

TRUNC(SYSDATE,'Q' 'THIS_
----------------- ------
01/01/18 00:00:00 THIS_Q
01/10/17 00:00:00 LAST_Q

There is probably a more elegant way to do it, but I've not had a lot of coffee yet.

[Updated on: Fri, 09 March 2018 04:11]

Report message to a moderator

Re: Get Current & Previous Quarter Data from table [message #668685 is a reply to message #668683] Fri, 09 March 2018 04:43 Go to previous messageGo to next message
ramya29p
Messages: 146
Registered: November 2007
Location: Chennai
Senior Member
But this is not working when I do it from the table using its date column. I have shared the sample query which I have tried.
Re: Get Current & Previous Quarter Data from table [message #668687 is a reply to message #668685] Fri, 09 March 2018 05:03 Go to previous message
flyboy
Messages: 1886
Registered: November 2006
Senior Member
Although you are talking about "passing" a parameter, you do not use it in comparisons at all.
The only thing which is not present in EMPLOYEE table is a VARCHAR2 literal '30-Sep-2016'.
WHERE effective_date BETWEEN add_months(effective_date,-3) AND effective_date
This is always true for non-NULL EFFECTIVE_DATE - it is always greater than 3 months before and equal to itself.

Maybe you should start to actually use the "passed" value. If it is midnight of the last day in the quarter, no further adjustments are needed.
-- sample data to mimic representative table content
with employee (eff_dt, employee_id) as 
  ( select last_day(add_months(trunc(sysdate), -3*level)), level from dual connect by level <= 15 )
  , input (passed_date) as ( select date '2016-09-30' from dual )
-- the query itself
select * from employee, input
where eff_dt between add_months(passed_date, -3) and passed_date
order by eff_dt;
The optimal way of the parameter usage depends on the exact "passing" mechanism (bind variable would be the best way).
Previous Topic: Oracle Partitioned Table get values
Next Topic: steps to create dynamic join query in oracle sql
Goto Forum:
  


Current Time: Mon Sep 28 17:56:12 CDT 2020