0

I have a Table "TABLE1" with the below sample data:

ROW_NO UNIQUEIDENTIFIER EFFECTIVEDATE ACTIVITYID AMT CALCAMT
1 AAA 5/10/2020 100 100 0
2 AAA 6/10/2020 100 20 0
3 AAA 8/17/2020 111 50 0
1 BBB 9/9/2021 100 150 0
2 BBB 10/11/2022 117 25 0
1 CCC 9/5/2023 100 150 0
2 CCC 10/2/2023 111 15 0
3 CCC 1/5/2025 100 20 0

I want the output be as:

ROW_NO UNIQUEIDENTIFIER EFFECTIVEDATE ACTIVITYID AMT CALCAMT
1 AAA 5/10/2020 100 100 100
2 AAA 6/10/2020 100 20 120
3 AAA 8/17/2020 111 50 70
1 BBB 9/9/2021 100 150 150
2 BBB 10/11/2022 117 25 100
1 CCC 9/5/2023 100 150 100
2 CCC 10/2/2023 111 15 85
3 CCC 1/5/2025 100 20 105

The query I have written using MODEL is:

with table1 (row_no,uniqueidentifier,effectivedate,activityid,amt,calcamt) as
(select 1,'AAA',cast('10-may-2020' as date),100,100,0
 from dual
 union all
 select 2,'AAA',cast('10-jun-2020' as date),100,20,0
 from dual
 union all
 select 3,'AAA',cast('17-aug-2020' as date),111,50,0
 from dual
 union all
 select 1,'BBB',cast('9-sep-2021' as date),100,150,0
 from dual
 union all
 select 2,'BBB',cast('11-oct-2022' as date),117,25,0
 from dual
 union all
 select 1,'CCC',cast('5-sep-2023' as date),100,100,0
 from dual
 union all
 select 2,'CCC',cast('2-oct-2023' as date),111,15,0
 from dual
 union all
 select 3,'CCC',cast('5-jan-2025' as date),100,20,0
 from dual
)
SELECT ROW_NO,UNIQUEIDENTIFIER,EFFECTIVEDATE,ACTIVITYID,AMT, CALCAMT
FROM TABLE1 
MODEL
  PARTITION BY(UNIQUEIDENTIFIER,EFFECTIVEDATE)
  DIMENSION BY (ROW_NO,ACTIVITYID,AMT)
  MEASURES(CALCAMT)
  RULES(
    CALCAMT[ROW_NO,ACTIVITYID,AMT] ORDER BY ROW_NO = CASE
              WHEN CV(ROW_NO) = 1 THEN
                CV(AMT)
              WHEN CV(ROW_NO) > 1 AND CV(ACTIVITYID) = 100 THEN
                CALCAMT[CV(ROW_NO) - 1, CV(ACTIVITYID) - 1, CV(AMT) - 1] + CV(AMT)
              WHEN CV(ROW_NO) > 1 AND CV(ACTIVITYID) = 111 THEN
                GREATEST(0,CALCAMT[CV(ROW_NO) - 1, CV(ACTIVITYID) - 1, CV(AMT) - 1] - CV(AMT))
              WHEN CV(ROW_NO) > 1 AND CV(ACTIVITYID) = 117 THEN
                GREATEST(0,CALCAMT[CV(ROW_NO) - 1, CV(ACTIVITYID) - 1, CV(AMT) - 1] - 2*CV(AMT))
                 END
        )
ORDER BY UNIQUEIDENTIFIER,EFFECTIVEDATE

The result of the query is not what I am expecting. The Result I am getting is:

ROW_NO UNIQUEIDENTIFIER EFFECTIVEDATE ACTIVITYID AMT CALCAMT
1 AAA 5/10/2020 100 100 100
2 AAA 6/10/2020 100 20 null
3 AAA 8/17/2020 111 50 null
1 BBB 9/9/2021 100 150 150
2 BBB 10/11/2022 117 25 null
1 CCC 9/5/2023 100 150 100
2 CCC 10/2/2023 111 15 null
3 CCC 1/5/2025 100 20 null

Can you please help?

Please note that: Activity 100 adds to previous CalcAmt or equals Amt if Row_No = 1

Activity 111 subtracts Amt of current row to previous Calc Amt

Activity 117 subtracts 2*Amt of current row to previous Calc Amt

Row_No = 1 will always have ActivityID as 100

Thanks

Improve this question
1
  • Please post your sample data and expected results as formatted text or tables, not as images. You also haven't shown the result you currently get, or why it is wrong. And the query you posted has syntax errors - please post your actual query and matching data/results, as a minimal reproducible example. Commented Jul 8 at 16:20

2 Answers 2

Reset to default
1

You over complicated your query: the rules are literally your specifications and the partition if of course only the uniqueidentifier: including the EFFECTIVEDATE will make of each partition a singleton row. Since row_no is continuous and starts at 1 in each uniqueidentifier partition you can use it alone as dimension.

SELECT *
FROM table1
MODEL
  PARTITION BY(uniqueidentifier)
  DIMENSION BY(row_no)
  MEASURES( effectivedate, activityno, amount, 0 as calcamnt)
  RULES(
    calcamnt[row_no = 1] = amount[cv()],
    calcamnt[row_no > 1] = case activityno[cv()] 
        when 100 then calcamnt[cv()-1] + amount[cv()] 
        when 111 then calcamnt[cv()-1] - amount[cv()] 
        when 117 then calcamnt[cv()-1] - 2 * amount[cv()] 
        end
    
)
ORDER BY uniqueidentifier, row_no
;

https://dbfiddle.uk/mjjZ1cYb

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

You can do it with the SUM analytic function and a CASE expression, without needing a MODEL clause:

SELECT t.*,
       SUM(
          CASE activityid
          WHEN 100 THEN amt
          WHEN 111 THEN -amt
          WHEN 117 THEN -2*amt
          END
       ) OVER (PARTITION BY uniqueidentifier ORDER BY row_no) AS calcamt
FROM   table1 t

Which, for the sample data:

CREATE TABLE table1 (row_no,uniqueidentifier,effectivedate,activityid,amt) as
 select 1,'AAA',DATE '2020-05-10',100,100 from dual union all
 select 2,'AAA',DATE '2020-06-10',100, 20 from dual union all
 select 3,'AAA',DATE '2020-08-17',111, 50 from dual union all
 select 1,'BBB',DATE '2021-09-09',100,150 from dual union all
 select 2,'BBB',DATE '2022-10-11',117, 25 from dual union all
 select 1,'CCC',DATE '2023-09-05',100,100 from dual union all
 select 2,'CCC',DATE '2023-10-02',111, 15 from dual union all
 select 3,'CCC',DATE '2025-01-05',100, 20 from dual

Outputs:

ROW_NO UNIQUEIDENTIFIER EFFECTIVEDATE ACTIVITYID AMT CALCAMT
1 AAA 2020-05-10 00:00:00 100 100 100
2 AAA 2020-06-10 00:00:00 100 20 120
3 AAA 2020-08-17 00:00:00 111 50 70
1 BBB 2021-09-09 00:00:00 100 150 150
2 BBB 2022-10-11 00:00:00 117 25 100
1 CCC 2023-09-05 00:00:00 100 100 100
2 CCC 2023-10-02 00:00:00 111 15 85
3 CCC 2025-01-05 00:00:00 100 20 105

fiddle

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.