1 Reply Latest reply: Feb 4, 2013 9:34 AM by Kári Georgsson RSS

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

      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
      STARTTIMEUserVolume
      15.1.201211130000
      16.1.20121116500
      17.1.2012222600
      17.1.201222210000

       

       

       

      Before
      Table_Static
      IdFreeVolumeVolumeUsedUserValidFromValidTo
      11000001111.1.20121.2.2012
      240001111.1.20121.2.2012
      4100003002221.1.20121.2.2012
      51000002221.1.201210.1.2012
      61000012002221.1.20121.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
      STARTTIMEUserVolumeFreeVolume
      15.1.20121113000010400
      16.1.201211165000
      17.1.2012222600600
      17.1.20122221000010000

       

       

       

      After
      Table_Static
      Idtable2.FreeVolumetable2.VolumeUsedUserValidFromValidTo
      110000100001111.1.20121.2.2012
      24004001111.1.20121.2.2012
      410000100002221.1.20121.2.2012
      51000002221.1.201210.1.2012
      61000021002221.1.20121.2.2012

       

       

       

      I hope you can help with this.

       

      regards, Kari