SQL & PL/SQL

Hello All,

I am trying to populate results for three fields (TOTAL_DUE, PREVIOUS_BALANCE and BALANCE_CARRIED_FORWARD) in a select statement but the calculations have fields that need previous fields to populate the correct results. I have been trying to use sub queries but I have not had any success with results.  Can the Alias be used possibly? Any help is definitely appreciated.

Here is the create table script.

CREATE TABLE TBL_CALCULATED_FEE

(

   SID                   NUMBER,

   ORGANIZATION_ID       NUMBER,

   ORGANIZATION          VARCHAR2 (32 BYTE),

   SESSION_MONTH         NUMBER,

   SESSION_YEAR          NUMBER,

   BASIS_FOR_FEE         NUMBER,

   BASIS_FOR_FEE_TOTAL   NUMBER,

   PAYMENTS_RECEIVED     NUMBER,

   CALCULATED_FEE        NUMBER,

   ADDITIONAL_AMOUNT     NUMBER

)

Here are the insert statements.

INSERT INTO TBL_CALCULATED_FEE

     VALUES (247750,

             243480,

             'Bombers Test',

             3,

             2014,

             0,

             1027.5,

             98.38,

             15.4125,

             0);

INSERT INTO TBL_CALCULATED_FEE

     VALUES (247740,

             243480,

             'Bombers Test',

             3,

             2014,

             0,

             1027.5,

             0,

             0,

             0);

INSERT INTO TBL_CALCULATED_FEE

     VALUES (247741,

             243480,

             'Bombers Test',

             3,

             2014,

             0,

             1027.5,

             0,

             0,

             0);

This is the original statement which does not populate.

SELECT SID,

       ORGANIZATION_ID,

       ORGANIZATION,

       SESSION_MONTH,

       SESSION_YEAR,

       BASIS_FOR_FEE,

       BASIS_FOR_FEE_TOTAL,

       PAYMENTS_RECEIVED,

       CALCULATED_FEE,

       ADDITIONAL_AMOUNT,

         (LAG (

               TOTAL_DUE)

            OVER (PARTITION BY ORGANIZATION_ID

                  ORDER BY SESSION_YEAR, SESSION_MONTH)) AS PREVIOUS_BALANCE,

         (PREVIOUS_BALANCE) - (PAYMENTS_RECEIVED) AS BALANCE_CARRIED_FORWARD,

          (PREVIOUS_BALANCE - PAYMENTS_RECEIVED + (BALANCE_CARRIED_FORWARD)

           + CALCULATED_FEE + ADDITIONAL_AMOUNT) AS TOTAL_DUE

  FROM TBL_CALCULATED_FEE

-I tried adding the ((PREVIOUS_BALANCE) - (PAYMENTS_RECEIVED)) for BALANCE_CARRIED_FORWARD

-For the PREVIOUS_BALANCE I added the (LAG (

               TOTAL_DUE)

            OVER (PARTITION BY ORGANIZATION_ID

                  ORDER BY SESSION_YEAR, SESSION_MONTH)) to calculate that field but now the TOTAL_DUE is invalid.

-This does not seem like the correct path to take, errors and not clear to follow.

SELECT SID,

       ORGANIZATION_ID,

       ORGANIZATION,

       SESSION_MONTH,

       SESSION_YEAR,

       BASIS_FOR_FEE,

       BASIS_FOR_FEE_TOTAL,

       PAYMENTS_RECEIVED,

       CALCULATED_FEE,

       ADDITIONAL_AMOUNT,

         (LAG (

               TOTAL_DUE)

            OVER (PARTITION BY ORGANIZATION_ID

                  ORDER BY SESSION_YEAR, SESSION_MONTH)) AS PREVIOUS_BALANCE,

         ((LAG (

               TOTAL_DUE)

            OVER (PARTITION BY ORGANIZATION_ID

                  ORDER BY SESSION_YEAR, SESSION_MONTH))) - (PAYMENTS_RECEIVED) AS BALANCE_CARRIED_FORWARD,

          ((LAG (

               TOTAL_DUE)

            OVER (PARTITION BY ORGANIZATION_ID

                  ORDER BY SESSION_YEAR, SESSION_MONTH)) + PAYMENTS_RECEIVED + (((LAG (

               TOTAL_DUE)

            OVER (PARTITION BY ORGANIZATION_ID

                  ORDER BY SESSION_YEAR, SESSION_MONTH))) - (PAYMENTS_RECEIVED))

           + CALCULATED_FEE + ADDITIONAL_AMOUNT) AS TOTAL_DUE

  FROM TBL_CALCULATED_FEE

The Previous Balance will not populate any results for this data but the Balance Carried Forward (-98) and Total Due (-82.97) will.  Here are the results that I would like to populate if possible.