SQL Question - Adding more rows to the resultset

From: gokulkumar gopal <gokulkumar.gopal_at_gmail.com>
Date: Sun, 8 Mar 2009 03:13:33 +0100
Message-ID: <9277b4900903071813g357e10fkd1611cc94fb83d35_at_mail.gmail.com>

I am trying to solve a migration problem in sql. Test scripts below if someone would like to try and give me a hand. The issue, how to carry over the result of the previous row to the next (well, not lag or lead exactly).
In this example below, a particular transaction has a set of available set of other transaction ids it can use. Here, usedid has used 723, but can use only 48. So, the availid 1266 has nothing to carry over to the next level and so on. The usedid 2333 has used 386 with nothing carried over from previous, but after using all of its available transactions, it can carry over 380 to the next level (68+48+50+600 - 386). The usedid 2654 has used 32, but can already make use of 380 carried from previous level. Also, the current trasactions 32 holds 2417, 2516, 2640,2652 must be made
available to the next usedids (incuding the 380 - 32 carry over).

USEDID  AVAILID    USED      AVAIL
1309    1266    723     48
2022    1387    347     47
2022    1497    347     49
2022    1610    347     40
2022    1716    347     62
2022    1827    347     43
2022    1937    347     44
2333    2067    386     68
2333    2175    386     48
2333    2291    386     50
2333    2304    386     600
2654    2417    32      52
2654    2516    32      42
2654    2640    32      43
2654    2652    32      250
3006    2771    308     41
3006    2880    308     44
3006    2992    308     40
3362    3116    217     43
3362    3223    217     41
3362    3339    217     61
3727    3480    647     15
3727    3578    647     15
3727    3699    647     15
3727    3703    647     600

create table TEMP_TEST
(

  USED_ID  NUMBER,
  AVAIL_ID NUMBER,
  USED     NUMBER,

  AVAIL NUMBER
);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (1309, 1266, 723, 48);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2022, 1387, 347, 47);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2022, 1497, 347, 49);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2022, 1610, 347, 40);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2022, 1716, 347, 62);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2022, 1827, 347, 43);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2022, 1937, 347, 44);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2333, 2067, 386, 68);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2333, 2175, 386, 48);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2333, 2291, 386, 50);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2333, 2304, 386, 600);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2654, 2417, 32, 52);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2654, 2516, 32, 42);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2654, 2640, 32, 43);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (2654, 2652, 32, 250);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3006, 2771, 308, 41);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3006, 2880, 308, 44);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3006, 2992, 308, 40);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3362, 3116, 217, 43);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3362, 3223, 217, 41);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3362, 3339, 217, 61);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3727, 3480, 647, 15);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3727, 3578, 647, 15);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3727, 3699, 647, 15);

insert into temp_test (USED_ID, AVAIL_ID, USED, AVAIL) values (3727, 3703, 647, 600);

I have tried a few options but dont know how to make up new rows or to move few transactions to the next level.

Is this possible in SQL ?

Gokul

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Mar 07 2009 - 20:13:33 CST