Input Output ResultsHope you are doing good.
I am stuck in a requirement where I need to have records distributed into multiple records based on the duration I get it from a linking table.
Suppose I have a volume of 100 and duration I am getting is 20 months linking table then my output should have 20 records of each 5(100/20). Could you please help me with the query how to do this SQL.
CodePudding user response：
The WITH clause is here just to generate some sample data and, as such, it is not a part of the answer.
You can join the tables ON PRODUCT columns, limit the iterations using LEVEL <= DURATION, group the data and show the amount either as Min, Max or Avg of COST/DURATION rounded to two decimals. I put all of the data in the select list. Here is the complete code with the result. Regards...
WITH
t_duration AS
(
Select 'A' "PRODUCT", 10 "DURATION" From Dual Union All
Select 'B' "PRODUCT", 6 "DURATION" From Dual Union All
Select 'C' "PRODUCT", 4 "DURATION" From Dual
),
t_cost AS
(
Select 'A' "PRODUCT", 100 "COST" From Dual Union All
Select 'B' "PRODUCT", 50 "COST" From Dual Union All
Select 'C' "PRODUCT", 40 "COST" From Dual
)
SELECT
LEVEL "MONTH_ORDER_NUMBER",
d.PRODUCT "PRODUCT",
d.DURATION "DURATION",
c.COST "COST",
Round(Avg(c.COST / d.DURATION), 2) "AVG_MONTHLY_AMOUNT",
Round(Max(c.COST / d.DURATION), 2) "MAX_MONTHLY_AMOUNT",
Round(Min(c.COST / d.DURATION), 2) "MIN_MONTHLY_AMOUNT"
FROM
t_duration d
INNER JOIN
t_cost c ON(c.PRODUCT = d.PRODUCT)
CONNECT BY LEVEL <= d.DURATION
GROUP BY
d.PRODUCT, d.DURATION, c.COST, LEVEL
ORDER BY
d.PRODUCT, LEVEL
--
-- R e s u l t
--
-- MONTH_ORDER_NUMBER PRODUCT DURATION COST AVG_MONTHLY_AMOUNT MAX_MONTHLY_AMOUNT MIN_MONTHLY_AMOUNT
-- ------------------ ------- ---------- ---------- ------------------ ------------------ ------------------
-- 1 A 10 100 10 10 10
-- 2 A 10 100 10 10 10
-- 3 A 10 100 10 10 10
-- 4 A 10 100 10 10 10
-- 5 A 10 100 10 10 10
-- 6 A 10 100 10 10 10
-- 7 A 10 100 10 10 10
-- 8 A 10 100 10 10 10
-- 9 A 10 100 10 10 10
-- 10 A 10 100 10 10 10
-- 1 B 6 50 8.33 8.33 8.33
-- 2 B 6 50 8.33 8.33 8.33
-- 3 B 6 50 8.33 8.33 8.33
-- 4 B 6 50 8.33 8.33 8.33
-- 5 B 6 50 8.33 8.33 8.33
-- 6 B 6 50 8.33 8.33 8.33
-- 1 C 4 40 10 10 10
-- 2 C 4 40 10 10 10
-- 3 C 4 40 10 10 10
-- 4 C 4 40 10 10 10
CodePudding user response：
That looks as if ntile would do the job (at least, that's how I understood the question).
Here's a table with 100 rows (that's your "volume of 100").
SQL> create table test (id) as
2 select level from dual connect by level <= 100;
Table created.
You'd then pass 20 (that's "duration of 20 months") to ntile and get the result - see the grp column, having 20 groups, each of them having 5 rows:
SQL> select id, ntile(20) over (order by id) grp
2 from test
3 order by id;
ID GRP
---------- ----------
1 1
2 1
3 1
4 1
5 1
6 2
7 2
8 2
9 2
10 2
11 3
12 3
13 3
14 3
15 3
<snip>
91 19
92 19
93 19
94 19
95 19
96 20
97 20
98 20
99 20
100 20
100 rows selected.
SQL>
[EDIT, based on new information]
With sample tables you posted:
SQL> with
2 duration (product, duration) as
3 (select 'A', 10 from dual union all
4 select 'B', 6 from dual union all
5 select 'C', 4 from dual
6 ),
7 cost (product, cost) as
8 (select 'A', 100 from dual union all
9 select 'B', 50 from dual union all
10 select 'C', 40 from dual
11 )
query would look like this:
12 select d.product,
13 c.cost / d.duration as amount
14 from duration d join cost c on c.product = d.product
15 cross join table(cast(multiset(select level from dual
16 connect by level <= d.duration
17 ) as sys.odcinumberlist))
18 order by d.product;
PRODUCT AMOUNT
---------- ----------
A 10
A 10
A 10
A 10
A 10
A 10
A 10
A 10
A 10
A 10
B 8,33333333
B 8,33333333
B 8,33333333
B 8,33333333
B 8,33333333
B 8,33333333
C 10
C 10
C 10
C 10
20 rows selected.
SQL>