Home » SQL & PL/SQL » SQL & PL/SQL » Split the Sum in 03 Portions (Oracle 11G, Windows 2003)
| Split the Sum in 03 Portions [message #649278] |
Mon, 21 March 2016 02:27  |
mmohsinaziz
Messages: 110
Registered: May 2012
|
Senior Member |
|
|
Dear Seniors,
I need your help in writing the query to find out the required output. I am sure, as usual you guys shall help me.
CREATE TABLE test
(
acode NUMBER(4),
vdate DATE,
qty NUMBER(5,3)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
Sample data is
insert into test values (1001, to_date('02-feb-16','dd-mon-rr'),55.214);
insert into test values (1001, to_date('05-feb-16','dd-mon-rr'),65.242);
insert into test values (1001, to_date('12-feb-16','dd-mon-rr'),78.102);
insert into test values (1001, to_date('15-feb-16','dd-mon-rr'),35.742);
insert into test values (1001, to_date('25-feb-16','dd-mon-rr'),64.852);
insert into test values (1001, to_date('26-feb-16','dd-mon-rr'),45.106);
insert into test values (1001, to_date('29-feb-16','dd-mon-rr'),74.52);
insert into test values (1002, to_date('03-feb-16','dd-mon-rr'),58.582);
insert into test values (1002, to_date('08-feb-16','dd-mon-rr'),88.147);
insert into test values (1002, to_date('22-feb-16','dd-mon-rr'),62.524);
insert into test values (1002, to_date('28-feb-16','dd-mon-rr'),42.151);
insert into test values (1003, to_date('12-feb-16','dd-mon-rr'),25.143);
select acode,sum(qty) qty
from test
where vdate between to_date('01-feb-16','dd-mon-rr') and to_date('29-feb-16','dd-mon-rr')
group by acode
order by acode;
ACODE QTY
------- ----------
1001 418.778
1002 251.404
1003 25.143
Requirement is to break the total in 03 portions. Ist portion will be of qty 200 and its rate will be 30,
2nd portion will be qty 100 and its rate will be 20
3rd portion will be rest of qty its rate will be 10
Output will be like this
Acode Description QTY Rate Amount
------- ------------- ---------- -------- ---------
1001 Ist Portion 200 30 6000
1001 2nd Portion 100 20 2000
1001 Last Portion 118.778 10 1187.8
1002 Ist Portion 200 30 6000
1002 2nd Portion 51.404 20 1228.08
1003 Ist Portion 25.143 30 754.29
I will be very thankful to you for your kind support.
|
|
|
|
| Re: Split the Sum in 03 Portions [message #649280 is a reply to message #649278] |
Mon, 21 March 2016 02:50  |
 |
Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> break on acode dup skip 1
SQL> with
2 data as (
3 select acode,sum(qty) qty
4 from test
5 where vdate between to_date('01-feb-16','dd-mon-rr')
6 and to_date('29-feb-16','dd-mon-rr')
7 group by acode
8 ),
9 portions as (
10 select level portion from dual connect by level <= 3
11 )
12 select acode,
13 decode(portion,
14 1, 'Ist Portion',
15 2, '2nd Portion',
16 3, 'Last Portion')
17 description,
18 decode(portion,
19 1, least(200, qty),
20 2, least(100, qty-200),
21 3, qty-300)
22 qty,
23 decode(portion, 1,30, 2,20, 3,10) rate,
24 decode(portion,
25 1, 30*least(200, qty),
26 2, 20*least(100, qty-200),
27 3, 10*(qty-300))
28 amount
29 from data, portions
30 where qty-decode(portion, 1,0, 2,200, 3,300) > 0
31 order by acode, portion
32 /
ACODE DESCRIPTION QTY RATE AMOUNT
---------- ------------ ---------- ---------- ----------
1001 Ist Portion 200 30 6000
1001 2nd Portion 100 20 2000
1001 Last Portion 118.778 10 1187.78
1002 Ist Portion 200 30 6000
1002 2nd Portion 51.404 20 1028.08
1003 Ist Portion 25.143 30 754.29
6 rows selected.
|
|
|
|
|
|
|
|
| Re: Split the Sum in 03 Portions [message #649292 is a reply to message #649282] |
Mon, 21 March 2016 08:49 |
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Grouping sets solution:
with t as (
select acode,
case group_id()
when 0 then least(200,sum(qty))
when 1 then least(100,sum(qty) - 200)
when 2 then sum(qty) - 300
end qty,
case group_id()
when 0 then 'Ist Portion'
when 1 then '2nd Portion'
else 'Last Portion'
end description,
group_id() portion
from test
where vdate between date '2016-02-01' and date '2016-02-29'
group by grouping sets((acode),(acode),(acode))
)
select acode,
description,
qty,
30 - 10 * portion rate,
qty * (30 - 10 * portion) amount
from t
where qty > 0
order by acode,
portion
/
ACODE DESCRIPTION QTY RATE AMOUNT
---------- ------------ ---------- ---------- ----------
1001 Ist Portion 200 30 6000
1001 2nd Portion 100 20 2000
1001 Last Portion 118.778 10 1187.78
1002 Ist Portion 200 30 6000
1002 2nd Portion 51.404 20 1028.08
1003 Ist Portion 25.143 30 754.29
SY.
|
|
|
|
| Re: Split the Sum in 03 Portions [message #649294 is a reply to message #649292] |
Mon, 21 March 2016 09:23 |
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Model solution:
with t as (
select acode,
sum(qty) qty
from test
where vdate between date '2016-02-01' and date '2016-02-29'
group by acode
)
select acode,
case portion
when 1 then 'Ist Portion'
when 2 then '2nd Portion'
else 'Last Portion'
end description,
qty,
40 - 10 * portion rate,
qty * (40 - 10 * portion) amount
from t
model
partition by(acode)
dimension by(1 portion)
measures(qty,qty q)
rules(
qty[for portion from 1 to least(3,trunc(qty[1] / 100)) increment 1] = case cv(portion)
when 1 then least(200,q[1])
when 2 then least(100,q[1] - 200)
else q[1] - 300
end
)
order by acode,
portion
/
ACODE DESCRIPTION QTY RATE AMOUNT
---------- ------------ ---------- ---------- ----------
1001 Ist Portion 200 30 6000
1001 2nd Portion 100 20 2000
1001 Last Portion 118.778 10 1187.78
1002 Ist Portion 200 30 6000
1002 2nd Portion 51.404 20 1028.08
1003 Ist Portion 25.143 30 754.29
6 rows selected.
SQL>
SY.
[Updated on: Tue, 22 March 2016 09:40] Report message to a moderator |
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 15:36:35 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
