Get Current & Previous Quarter Data from table [message #668679] |
Fri, 09 March 2018 03:39 |
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 #668683 is a reply to message #668681] |
Fri, 09 March 2018 04:10 |
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 #668687 is a reply to message #668685] |
Fri, 09 March 2018 05:03 |
flyboy
Messages: 1903 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).
|
|
|