Home » SQL & PL/SQL » SQL & PL/SQL » Spreading the data evenly (Oracle 9i, 9.2.0.1.0, RHEL 2.1 AS)
Spreading the data evenly [message #409200] Sat, 20 June 2009 06:02 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear All,

I have a table with following data. i want result like mention below.Is it possible to through sql. please suggest me.

create table temp(totalblend number,kgsperblend number,id number,actualkgs number);

insert into temp values(6,20,17,11);
insert into temp values(6,20,18,12);
insert into temp values(6,20,19,13);
insert into temp values(6,20,20,11);
insert into temp values(6,20,21,12);
insert into temp values(6,20,22,12);
insert into temp values(6,20,23,14);
insert into temp values(6,20,24,22);
insert into temp values(6,20,25,13);
commit;

select * from temp;



i want result like this.

xxxxx id kgs
1 17 11
1 18 9

2 18 3
2 19 13
2 20 4

3 20 7
3 21 12
3 22 1

4 22 11
4 23 9

5 23 5
5 24 15

6 24 7
6 25 13


Thanks
Re: Spreading the data evenly [message #409201 is a reply to message #409200] Sat, 20 June 2009 06:48 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
What is the logic behind that desired resultset?
Re: Spreading the data evenly [message #409202 is a reply to message #409200] Sat, 20 June 2009 06:54 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear pablolee

logic was sum of actualkgs is 120 and it has to be devided in 6 [totalblend value]. it means per blend has evenly devided 20kgs[kgsperblend value].so each new lot is generated witch will not more than 20 kgs.


Regards
Re: Spreading the data evenly [message #409212 is a reply to message #409200] Sat, 20 June 2009 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The following topics will help you:
Puzzle n°03 - Sharing articles
Puzzle n°04 - Evenly share batches of articles into groups

Regards
Michel

[Updated on: Sat, 20 June 2009 09:33]

Report message to a moderator

Re: Spreading the data evenly [message #409306 is a reply to message #409200] Mon, 22 June 2009 01:45 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

I have tried that puzzle but i'm not getting the result which i want, u may please help me. here i once again post the detail what i tried.
CREATE TABLE batches (NO_OF_LOTS NUMBER,batch_id INTEGER,avail_qty INTEGER);

INSERT INTO batches VALUES(6,17,11);      
INSERT INTO batches VALUES(6,19,11);
INSERT INTO batches VALUES(6,17,12);
INSERT INTO batches VALUES(6,19,12);
INSERT INTO batches VALUES(6,19,12);
INSERT INTO batches VALUES(6,18,13);
INSERT INTO batches VALUES(6,46,13);
INSERT INTO batches VALUES(6,32,14);
INSERT INTO batches VALUES(6,32,22);
COMMIT;

SELECT ROWNUM,BATCH_ID, AVAIL_QTY,XX + D
FROM (SELECT BATCH_ID,
             AVAIL_QTY,
             XX,
             DECODE (SIGN (LEAST (BAL, NVL (LAG (ZZ) OVER (ORDER BY AVAIL_QTY), Y))),
               -1, 0,
               LEAST (BAL, NVL (LAG (ZZ) OVER (ORDER BY AVAIL_QTY), Y))
             ) D
FROM (SELECT BATCH_ID,
             AVAIL_QTY,
             XX,
             Y,
             BAL,
             Y - SUM (BAL) OVER (ORDER BY BAL) ZZ
FROM (WITH DATASET AS
      (SELECT SUM (AVAIL_QTY)  CNT,
              20 N
       FROM BATCHES)
      SELECT BATCH_ID,
             AVAIL_QTY,
             LEAST (AVAIL_QTY, ROUND ((N / CNT))) XX,
             N - SUM (LEAST (AVAIL_QTY, ROUND ((N / CNT)))) OVER (ORDER BY NULL) Y,
             AVAIL_QTY - LEAST (AVAIL_QTY, ROUND ((N / CNT))) BAL
      FROM BATCHES, DATASET)))


ROWNUM  BATCH_ID  AVAIL_QTY       XX+D
------ --------- ---------- ----------
  1           17         11         11
  2           19         11          0
  3           17         12          0
  4           19         12          0
  5           19         12          0
  6           18         13          0
  7           46         13          0
  8           32         14          0
  9           32         22          0




Actually i want data to be spreading for six lots[no_of_lots value].each lot have 20 [&num/n value] articles. in my case for lots should be like this.

lot batch_id xx+d
1 17 11
1 19 9 --taken from rownum=2

2 19 2 --remaining of available 11-9,rownum=2
2 17 12 --add from available 12,rownum=3
2 19 6 --add from available 12,rownum=4

3 19 6 --remaining of available 11-9,rownum=4
3 19 12 --add from available 12,rownum=5
3 18 2 --add from available 13,rownum=6

and so on till all rows[9].

Please suggest me how can i do this.

Thanks & Regards

Jimit
Re: Spreading the data evenly [message #409330 is a reply to message #409306] Mon, 22 June 2009 03:25 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Here's a little something I knocked together:
create table test_228(totalblend number,kgsperblend number,id number,actualkgs number);

insert into test_228 values(6,20,17,11);
insert into test_228 values(6,20,18,12);
insert into test_228 values(6,20,19,13);
insert into test_228 values(6,20,20,11);
insert into test_228 values(6,20,21,12);
insert into test_228 values(6,20,22,12);
insert into test_228 values(6,20,23,14);
insert into test_228 values(6,20,24,22);
insert into test_228 values(6,20,25,13);
commit;

select grp
      ,id
      ,count(*)
from (select id
            ,actualkgs
            ,1  single_kg
            ,ceil(row_number() over (order by id,num)/20) grp
            ,num
      from  (select level num
             from  (select max(actualkgs) actualkgs from test_228) gen
             connect by level <= gen.actualkgs) src
            ,test_228
      where  src.num <= actualkgs)
group by id,grp      
order by id;


Gives the following results:
1	17	11
1	18	9
2	18	3
2	19	13
2	20	4
3	20	7
3	21	12
3	22	1
4	22	11
4	23	9
5	23	5
5	24	15
6	24	7
6	25	13
Re: Spreading the data evenly [message #409332 is a reply to message #409200] Mon, 22 June 2009 03:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> break on xxxxx skip 1
SQL> with 
  2    lines as ( select level line from dual connect by level <= 25 ),
  3    flagged as (
  4      select id, 
  5             trunc((row_number() over (order by id, line)-1)/kgsperblend) + 1 xxxxx
  6      from temp, lines
  7      where line <= actualkgs 
  8    )
  9  select xxxxx, id, count(*) kgs
 10  from flagged
 11  group by xxxxx, id
 12  order by xxxxx, id
 13  /
     XXXXX         ID        KGS
---------- ---------- ----------
         1         17         11
                   18          9

         2         18          3
                   19         13
                   20          4

         3         20          7
                   21         12
                   22          1

         4         22         11
                   23          9

         5         23          5
                   24         15

         6         24          7
                   25         13

Regards
Michel
Re: Spreading the data evenly [message #409348 is a reply to message #409200] Mon, 22 June 2009 04:27 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks to all . You all are real genius.
but i have small problem with my values, if i change one my value to 22 from 22.5 and 14 to 13.5 than answer is not right. can you suggest me why it happen. i tried with sum in place of count but it is not working.

 update temp set  ACTUALKGS=22.5 where id = 24;
 update temp set  ACTUALKGS=13.5 where id = 23;
commit;



Regards

Jimit

[Updated on: Mon, 22 June 2009 04:31]

Report message to a moderator

Re: Spreading the data evenly [message #409351 is a reply to message #409348] Mon, 22 June 2009 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a different problem.
You have to precise your conditions first.
What can be the values?
And what if totalblend*kgsperblend <> sum(actualkgs)?
And what if totalblend and/or kgsperblend are not the same in all rows?
...

Regards
Michel

[Updated on: Mon, 22 June 2009 04:35]

Report message to a moderator

Re: Spreading the data evenly [message #409353 is a reply to message #409200] Mon, 22 June 2009 04:44 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Sorry Michel, it is my mistake that i have not precise in values.actually i'm little concerned for my other values.so i miss the variation in values. and answer to your question i have check my live data and it is always
totalblend*kgsperblend <> sum(actualkgs) will be the same.
and totalblend and/or kgsperblend are the same in all rows.
i really admit that was my mistake.

Regards

Jimit
Re: Spreading the data evenly [message #409356 is a reply to message #409348] Mon, 22 June 2009 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm guessing that you've run my solution, but not understood it.

The trick at the hear of it is to split each row in your data down into <actualkgs> seperate rows, each of which represents 1kg.
Then all I have to do is to group these rows together in groups of 20 and see how many rows are there from each id.

If you want a 1/2kg granularity, then you need to change the SRC inline view to return twice as many rows, with values of 0.5, 1, 1.5, 2, 2.5....
Re: Spreading the data evenly [message #409361 is a reply to message #409353] Mon, 22 June 2009 05:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the domain of values? As JRowbottom said, if it is 1/2 kg you have to double the lines, if it is 1/10 kg you have to generate them 10 times and so on.

Regards
Michel
Re: Spreading the data evenly [message #409363 is a reply to message #409200] Mon, 22 June 2009 05:51 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
dear JRowbottom

i have tried this but still i'm not getting where is the problem.please suggest me either i have do something else or keep try on this suggestion.

 select grp
       ,id
       ,count(*)
 from (select id
             ,sum(actualkgs)
             ,1/100  single_kg
             ,ceil(row_number() over (order by id,num)/20) grp
             ,num
       from  (select level num
              from  (select sum(actualkgs)*100 actualkgs from temp) gen
              connect by level <= gen.actualkgs) src
             ,temp
       where  src.num <= actualkgs group by id,num)
 group by id,grp
 order by id



Regards

jimit
Re: Spreading the data evenly [message #409365 is a reply to message #409200] Mon, 22 June 2009 05:54 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

my values may vary to 1.45,2.64,6.96 or 66.06 for actualkgs as well as kgsperblend value. please suggest me something.

Thanks for quick replies.

Regards

Jimit
Re: Spreading the data evenly [message #409373 is a reply to message #409363] Mon, 22 June 2009 06:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
May I suggest that you take the time to dissect my original solution, and understand how the inner query works, and then look at the inner two queries together, and so on.

Your current approach appears to involve making changes at random, and hoping things will work - eg you've changed the innermost aggregation function from a MAX to a SUM, and added an entire (and entirely unneeded) level of aggregation.

I shouldn't do this, as I'm fairly certain that you'll just take this code and hack it about, rather than actually following my advice, but here is how to get a 1/2 kg granularity:
create table test_228(totalblend number,kgsperblend number,id number,actualkgs number);

insert into test_228 values(6,20,17,11.5);
insert into test_228 values(6,20,18,12.5);
insert into test_228 values(6,20,19,13);
insert into test_228 values(6,20,20,11);
insert into test_228 values(6,20,21,12);
insert into test_228 values(6,20,22,12);
insert into test_228 values(6,20,23,14);
insert into test_228 values(6,20,24,22);
insert into test_228 values(6,20,25,13);
commit;

select grp
      ,id
      ,count(*)/2
from (select id
            ,actualkgs
            ,ceil(row_number() over (order by id,num)/(20*2)) grp
            ,num
      from  (select level/2 num
             from  (select max(actualkgs) actualkgs from test_228) gen
             connect by level/2 <= gen.actualkgs) src
            ,test_228
      where  src.num <= actualkgs)
group by id,grp      
order by id;

The row-generator query has been changed to generate 2 rows per kilogram, we group then into sets of 40 (40 * 0.5 kg = 20kg) and then divide the number of rows by 2 in the outer query to return the value to the correct unit.
Re: Spreading the data evenly [message #409379 is a reply to message #409200] Mon, 22 June 2009 06:35 Go to previous message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear JRowbottom,Michel

it is working.Thanks for your help.

Regards

Jimit
Previous Topic: how to validate the input for alphanumaric in oracle 9i
Next Topic: UTL_SMTP unable to send attachment
Goto Forum:
  


Current Time: Sat Dec 10 20:53:09 CST 2016

Total time taken to generate the page: 0.10833 seconds