You Asked
Hi Tom, I have just came across a requirement, where I need to identify the increasing amount. say I have a table for quarterly expenses and I need to identify the dept which has increasing expense by 105 or more from previous quarter, and if any dept is breaking the rule for any quarter it should not appear in result.
create table expense (dept_no number, quarter varchar(30),expense number);
insert into expense values(1,'Q1',100);
insert into expense values(1,'Q2',110);
insert into expense values(1,'Q3',121);
insert into expense values(1,'Q4',134);
insert into expense values(2,'Q1',100);
insert into expense values(2,'Q2',110);
insert into expense values(2,'Q3',115);
insert into expense values(2,'Q4',130);
o/p should contain all records for dept_no 1, but no record for dept_no 2 , as it breaks the rule for quarter Q3.
I tried SELECT dept_no,quarter,expense
FROM (select dept_no,quarter,expense, expense*1.1 incr_expense from expense)expense1
-- where dept_no = 1
START WITH quarter = 'Q1'
CONNECT BY NOCYCLE PRIOR expense < incr_expense AND PRIOR dept_no = dept_no , but it is giving multiple lines(includes record from dept_no 2 also)
and select A.* from
(select * from expense )A,(select * from expense )B
WHERE A. dept_no = B.dept_no
AND A.quarter < B.quarter
AND A.expense *1.1 <= B.expense
order by 1,2,3 is giving duplicate records. I know, we can use DISTINCT in query. But can you please suggest any simple low cost query. Thanks --Anubha
and Connor said...
OK....your *text*, ie, "the dept which has increasing expense by 105" does not appear to align with your SQL, "A.expense *1.1 <= B.expense"
And both do not appear to align with you conclusion that: "o/p should contain all records for dept_no 1, but no record for dept_no 2 , as it breaks the rule for quarter Q3", because on either 5% or 10%, both depts have a pair of rows that would break that rule.
So I am going to assume the following: "A dept breaks the rule if it increases by more than 12%", and work with that.
Step 1- lets the get percentage change first:
SQL> select
2 e.*,
3 expense / lag(expense,1) over ( partition by dept_no order by quarter ) as pct
4 from expense e
5 /
DEPT_NO QUARTER EXPENSE PCT
---------- ------------------------------ ---------- ----------
1 Q1 100
1 Q2 110 1.1
1 Q3 121 1.1
1 Q4 134 1.10743802
2 Q1 100
2 Q2 110 1.1
2 Q3 115 1.04545455
2 Q4 130 1.13043478
8 rows selected.
Step 2- which depts exceed 12%
SQL> select distinct dept_no
2 from (
3 select
4 e.*,
5 expense / lag(expense,1) over ( partition by dept_no order by quarter ) as pct
6 from expense e
7 )
8 where pct > 1.12;
DEPT_NO
----------
2
Step 3- get all the rows NOT for that dept
SQL> select * from expense
2 where dept_no not in (
3 select distinct dept_no
4 from (
5 select
6 e.*,
7 expense / lag(expense,1) over ( partition by dept_no order by quarter ) as pct
8 from expense e
9 )
10 where pct > 1.12
11 );
DEPT_NO QUARTER EXPENSE
---------- ------------------------------ ----------
1 Q4 134
1 Q3 121
1 Q2 110
1 Q1 100
4 rows selected.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment