SQL Question - Adding rows to the resultset
Date: Sat, 7 Mar 2009 18:01:23 -0800 (PST)
Message-ID: <f4a25023-6609-4287-8719-7a52a15b0664_at_q9g2000yqc.googlegroups.com>
I am try 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 Received on Sat Mar 07 2009 - 20:01:23 CST