Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Anyone who could possible help ?