2 Replies Latest reply: Jun 21, 2011 9:54 AM by user2269823 RSS

    "Balance forward" SQL statement

    user2269823
      Has anyone developed a SQL statement that allows balances to be carried forward? Would a UNION query possibly work?

      Here's my example where I need to join 3 tables and show results. Based on the example data below, I am looking for a query that allows me to illustrate that the Acrobat 9 Installed Software instances are consuming a license capacity of 6, leaving a balance of 4 that can be applied to the Acrobat 8 Installed Software Instances.

      Table: LICENSE

      LICENSENUM     SWLICENSE          LICENSECAPACITY
      '1001'          'Adobe Acrobat 9'     '10'

      Table: LICENSESW

      LICENSENUM     SWPRODUCT     PRECEDENCE
      '1001'     'Acrobat 9'     '1'
      '1001'     'Acrobat 8'     '2'

      Table: INSTALLEDSW

      INSTALLEDSW
      'Acrobat 9'
      'Acrobat 9'
      'Acrobat 9'
      'Acrobat 9'
      'Acrobat 9'
      'Acrobat 9'
      'Acrobat 8'
      'Acrobat 8'
      'Acrobat 8'
      'Acrobat 8'
      'Acrobat 8'


      I can write a select to do total counts, but this does not allow me to carry a balance forward to be applied to Acrobat 8 (precedence 2).

      select LICENSE.SWLICENSE, LICENSE.LICENSECAPACITY, count(INSTALLEDSW.INSTALLEDSW) as INSTALLED_COUNT
      from LICENSE, LICENSESW, INSTALLEDSW
      where LICENSE.LICENSENUM = LICENSESW.LICENSENUM
      AND LICENSESW.SWPRODUCT = INSTALLEDSW.INSTALLEDSW
      group by LICENSE.SWLICENSE, LICENSE.LICENSECAPACITY;

      Results:
      SWLICENSE          LICENSECAPACITY          INSTALLED_COUNT
      'Adobe Acrobat 9'     '10'               '11'



      I need results that look like this, where the balance of 4 is carried forward after the first row:

      SWLICENSE          LICENSECAPACITY          INSTALLED_PRODUCT     INSTALLED_COUNT
      'Adobe Acrobat 9'     '6'               'Acrobat 9'          '6'
      'Adobe Acrobat 9'     '4'               'Acrobat 8'          '5'
        • 1. Re: "Balance forward" SQL statement
          BluShadow
          Not easy to follow your example data, but if I demonstrate what I think you are trying to achieve using the emp table, you may be able to see if it helps...
          SQL> select deptno, empno, ename
            2        ,count(*) over (partition by deptno) as dept_count
            3        ,count(*) over (partition by deptno) - row_number() over (partition by deptno order by empno) as remaining
            4  from   emp
            5  order by deptno, empno;
          
              DEPTNO      EMPNO ENAME      DEPT_COUNT  REMAINING
          ---------- ---------- ---------- ---------- ----------
                  10       7782 CLARK               3          2
                  10       7839 KING                3          1
                  10       7934 MILLER              3          0
                  20       7369 SMITH               5          4
                  20       7566 JONES               5          3
                  20       7788 SCOTT               5          2
                  20       7876 ADAMS               5          1
                  20       7902 FORD                5          0
                  30       7499 ALLEN               6          5
                  30       7521 WARD                6          4
                  30       7654 MARTIN              6          3
                  30       7698 BLAKE               6          2
                  30       7844 TURNER              6          1
                  30       7900 JAMES               6          0
          
          14 rows selected.
          using the analytical functions count() and row_number() (you can use others such as sum(), avg(), lag(), lead() etc. as required) you can do processing that carries forward, backwards or across "groups" (aka partitions) of data.
          • 2. Re: "Balance forward" SQL statement
            user2269823
            @BluShadow, thanks very much!

            I think this is what I need. I will do some testing with this.

            From your example, it looks like a "partition" acts like persistent data that can be used in later calculations.