Re: SQL Question - Adding rows to the resultset

From: Michael Austin <maustin_at_firstdbasource.com>
Date: Mon, 09 Mar 2009 14:20:19 -0500
Message-ID: <0Ydtl.8433$jZ1.4732_at_flpi144.ffdc.sbc.com>

Gokul wrote:
> 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

the functions SUM() comes to mind... Other than USER_ID, I see no way for your to correlate any value. Unless you use a date field, there is NO WAY for you to know which USED/AVAIL was "first". You really need to hire a DBA that understands database concepts - this is not a spreadsheet. Received on Mon Mar 09 2009 - 14:20:19 CDT