how to calculate quota with analytic function row by row?

Question

We have a problem with using analytic function in Oracle.

We want to calculate remaining_quota column from other columns. Our data is grouped by branch_id and region_id. Each branch and region has a quota value(In example 1077). If quota value is larger than remaining_quota value, we doesn't change remaining_quota value.

I hope following example has been descriptive.

enter image description here

We created a query to calculate remaining_quota with sum over partition by functions but we failed.

Sample query and result

SELECT   CUSTOMER_NUM, BRANCH_ID, REGION_ID, TOTAL_QUOTA, QUOTA,
                 TOTAL_QUOTA - 
                 SUM (CASE
                           WHEN TOTAL_QUOTA - QUOTA < 0
                              THEN 0
                           ELSE QUOTA
                        END) OVER (PARTITION BY BRANCH_ID, REGION_ID ORDER BY BRANCH_ID,
                    REGION_ID,DESC)      
                     AS REMAINING_QUOTA                     
            FROM MY_TABLE
           WHERE BRANCH_ID = 723 AND REGION_ID = 822
        ORDER BY BRANCH_ID,
                 REGION_ID DESC

enter image description here

Please help us to deal with this problem.

Thanks...

IMPROVE :

In fact, I discarded some columns from table because of decreasing query complexity.

Our test table has these columns.

  CUSTOMER_NUM        NUMBER,
  BRANCH_ID           NUMBER,
  ACCOUNT_NUM         NUMBER,
  TOTAL_QUOTA         NUMBER,
  QUOTA               NUMBER,
  REGION_ID           NUMBER,
  LOB_CODE            NUMBER,
  ESTIMATED_END_DATE  DATE,
  TOTAL_AMOUNT        NUMBER

We create data structure of this table in SQLFiddle. Link : http://sqlfiddle.com/#!4/94fe4/1/0

My original fail query is :

SELECT   CUSTOMER_NUM, BRANCH_ID, REGION_ID,LOB_CODE,ESTIMATED_END_DATE,TOTAL_AMOUNT, TOTAL_QUOTA, QUOTA,
                 TOTAL_QUOTA -
                 SUM (CASE
                           WHEN TOTAL_QUOTA - QUOTA < 0
                              THEN 0
                           ELSE QUOTA
                        END) OVER (PARTITION BY BRANCH_ID, REGION_ID, LOB_CODE ORDER BY BRANCH_ID,
                    REGION_ID,
                    LOB_CODE,
                    ESTIMATED_END_DATE,
                    TOTAL_AMOUNT DESC)  
                     AS REMAINING_QUOTA              
            FROM TEST_TABLE
           WHERE BRANCH_ID = 723 AND REGION_ID = 822
                 AND LOB_CODE = 934
        ORDER BY BRANCH_ID,
                 REGION_ID,
                 LOB_CODE,
                 ESTIMATED_END_DATE,
                 TOTAL_AMOUNT DESC

I modified your solution query which includes GREATEST function. But it is not enough for us. With this function if remaining quota is negative you set this value to zero. However, if remaining quota is negative, we don't want to decrease quota value. This value must remain as same.

We want to get a result like the following query: (We add "or QUOTA between 471 and 472" row for right result. There is not a row in this way actually )

SELECT   CUSTOMER_NUM, BRANCH_ID, REGION_ID,LOB_CODE,ESTIMATED_END_DATE,TOTAL_AMOUNT, TOTAL_QUOTA, QUOTA,
                 TOTAL_QUOTA -
                 SUM (CASE
                           WHEN TOTAL_QUOTA - QUOTA < 0
                      or QUOTA between 471 and 472
                              THEN 0
                           ELSE QUOTA
                        END) OVER (PARTITION BY BRANCH_ID, REGION_ID, LOB_CODE ORDER BY BRANCH_ID,
                    REGION_ID,
                    LOB_CODE,
                    ESTIMATED_END_DATE,
                    TOTAL_AMOUNT DESC)  
                     AS REMAINING_QUOTA              
            FROM TEST_TABLE
           WHERE BRANCH_ID = 723 AND REGION_ID = 822
                 AND LOB_CODE = 934
        ORDER BY BRANCH_ID,
                 REGION_ID,
                 LOB_CODE,
                 ESTIMATED_END_DATE,
                 TOTAL_AMOUNT DESC

enter image description here

score 2 · Answer 1 · answered Jul 11 '14 at 11:04

It seems like the basic query is:

SELECT CUSTOMER_NUM, BRANCH_ID, REGION_ID, TOTAL_QUOTA, QUOTA,
       TOTAL_QUOTA - SUM(QUOTA) OVER (PARTITION BY BRANCH_ID, REGION_ID
                                      ORDER BY BRANCH_ID, REGION_ID DESC
                                     ) AS REMAINING_QUOTA
FROM MY_TABLE
WHERE BRANCH_ID = 723 AND REGION_ID = 822
ORDER BY BRANCH_ID, REGION_ID DESC;

Let me note that the order by clauses are not stable, because there are duplicates. It would be nice to have some other column, such as an id or datetime value that would really make the ordering consistent.

Your seem to be concerned about negative remaining quotas. Handle that after the calculation. Just use the greatest()function:

SELECT CUSTOMER_NUM, BRANCH_ID, REGION_ID, TOTAL_QUOTA, QUOTA,
       GREATEST(0,
                TOTAL_QUOTA - SUM(QUOTA) OVER (PARTITION BY BRANCH_ID, REGION_ID
                                               ORDER BY BRANCH_ID, REGION_ID DESC
                                              )
               ) AS REMAINING_QUOTA
FROM MY_TABLE
WHERE BRANCH_ID = 723 AND REGION_ID = 822
ORDER BY BRANCH_ID, REGION_ID DESC;

thx for replying. But your answer isn't enough to solve real problem. I improved my question. Please look at it — neverwinter, Jul 12 '14 at 07:21

how to calculate quota with analytic function row by row?

1 Answers1