Advanced Scripting: How to count Free Volume down based on another table?

[#1]

Hi all,

I have a problem that I'm struggling with and I hope I could get some help here. I have attached a demo qvw file.

Let's say I have two tables: Table_Volume and Table_Static.

They look like this before my problem exists:

Before
Table_Volume
STARTTIME	User	Volume
15.1.2012	111	30000
16.1.2012	111	6500
17.1.2012	222	600
17.1.2012	222	10000

Before
Table_Static
Id	FreeVolume	VolumeUsed	User	ValidFrom	ValidTo
1	10000	0	111	1.1.2012	1.2.2012
2	400	0	111	1.1.2012	1.2.2012
4	10000	300	222	1.1.2012	1.2.2012
5	10000	0	222	1.1.2012	10.1.2012
6	10000	1200	222	1.1.2012	1.2.2012

I need the Volume in Table_Volume to use the FreeVolume if he has (FreeVolume-VolumeUsed) but it must be done in the right order (by Id)

So If I illustrate the first row in Table_Volume: Volume is 30000 and check the first row in Table_Static where User is 111 and I see the User got FreeVolume (FreeVolume-VolumeUsed is not 0).

So I need to update VolumeUsed to 10000 because Volume is more than he got left, but the User got another FreeVolume that he can use. So I need to update VolumeUsed there aswell.

On the other table Table_Volume I also need to create two columns with calculated values. You can see the user uses all of his freevolume in the first record and I need to calculate how much of each record is free.

These two tables would in the end look like this. (As you can see the starttime have to match between ValidFrom and ValidTo) IntervalMatch?

After
Table_Volume
STARTTIME	User	Volume	FreeVolume
15.1.2012	111	30000	10400
16.1.2012	111	6500	0
17.1.2012	222	600	600
17.1.2012	222	10000	10000

After
Table_Static
Id	table2.FreeVolume	table2.VolumeUsed	User	ValidFrom	ValidTo
1	10000	10000	111	1.1.2012	1.2.2012
2	400	400	111	1.1.2012	1.2.2012
4	10000	10000	222	1.1.2012	1.2.2012
5	10000	0	222	1.1.2012	10.1.2012
6	10000	2100	222	1.1.2012	1.2.2012

I hope you can help with this.

regards, Kari

[#2]

Anyone who could possible help ?